Results 1 to 6 of 6

Thread: VBA code to insert the qty to the respective cell.

  1. #1

    VBA code to insert the qty to the respective cell.



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi All,

    I need to display four years data that, previous year, current year, and next two year from current year, please check the output template in the sample file.

    i need the code to insert the input qty data to the cells corresponds to the year and months cell of the respective product.

    Can anybody suggest me Logic or VBA code to insert the data,in the Output template , for reference please see the Attached sample data file.

    actual file have thousands of records .
    Attached Files Attached Files

  2. #2
    Code:
    Public Sub CreateLayout()Dim ws As Worksheet
    Dim targetrow As Long
    Dim basecol As Long
    Dim lastrow As Long
    Dim i As Long
    
    
        Application.ScreenUpdating = False
        
        Set ws = Worksheets("Output Template")
        
        With Worksheets("Data")
        
            lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
            For i = 2 To lastrow
            
                targetrow = 0
                On Error Resume Next
                targetrow = Application.Match(.Cells(i, "A").Value, ws.Columns(1), 0)
                On Error GoTo 0
                If targetrow = 0 Then
                
                    targetrow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1
                    ws.Cells(targetrow, "A").Value = .Cells(i, "A").Value
                End If
                
                basecol = 0
                On Error Resume Next
                basecol = Application.Match(.Cells(i, "D").Value, ws.Rows(1), 0)
                On Error GoTo 0
                If basecol > 0 Then
                
                    ws.Cells(targetrow, basecol + .Cells(i, "C").Value - 1).Value = .Cells(i, "B").Value
                End If
            Next i
        End With
        
        Application.ScreenUpdating = True
    End Sub

  3. #3
    Quote Originally Posted by Bob Phillips View Post
    Code:
    Public Sub CreateLayout()Dim ws As Worksheet
    Dim targetrow As Long
    Dim basecol As Long
    Dim lastrow As Long
    Dim i As Long
    
    
        Application.ScreenUpdating = False
        
        Set ws = Worksheets("Output Template")
        
        With Worksheets("Data")
        
            lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
            For i = 2 To lastrow
            
                targetrow = 0
                On Error Resume Next
                targetrow = Application.Match(.Cells(i, "A").Value, ws.Columns(1), 0)
                On Error GoTo 0
                If targetrow = 0 Then
                
                    targetrow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1
                    ws.Cells(targetrow, "A").Value = .Cells(i, "A").Value
                End If
                
                basecol = 0
                On Error Resume Next
                basecol = Application.Match(.Cells(i, "D").Value, ws.Rows(1), 0)
                On Error GoTo 0
                If basecol > 0 Then
                
                    ws.Cells(targetrow, basecol + .Cells(i, "C").Value - 1).Value = .Cells(i, "B").Value
                End If
            Next i
        End With
        
        Application.ScreenUpdating = True
    End Sub

    Thanks BOB,

    For the code. if i have same product as PO and Forecast then how can i put the qty , i mean how to segregate the PO qty from Forecast qty and put the PO qty and Forecast qty in two rows separately.

    can you suggest the code for this problem please?

  4. #4

    VBA code for this My File output.

    Hi All,

    Please find the attached file ,

    in the attached file the Data tab have the products details like demand type, qty, month, Year are there.
    here demand type is two types for each product PO and Forecast. i need to insert the product qty as per the PO and Forecast in separate row in the data template sheet as per the month and year for the products.

    Can anybody please help me with the VBA code for that i have huge number of records to be filled up like this.
    Attached Files Attached Files
    Last edited by Bob Phillips; 2014-07-28 at 05:10 PM. Reason: Merged with original thread

  5. #5
    Code:
    Option Explicit
    
    Public Sub CreateLayout()
    Dim ws As Worksheet
    Dim targetrow As Long
    Dim basecol As Long
    Dim lastrow As Long
    Dim i As Long
    
    
    
    
        Application.ScreenUpdating = False
        
        Set ws = Worksheets("Output Template")
        
        With Worksheets("Data")
        
            lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
            For i = 2 To lastrow
            
                targetrow = 0
                On Error Resume Next
                targetrow = Application.Match(.Cells(i, "A").Value, ws.Columns(1), 0)
                On Error GoTo 0
                If targetrow = 0 Then
                
                    targetrow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1
                    ws.Cells(targetrow, "A").Value = .Cells(i, "A").Value
                    ws.Cells(targetrow, "B").Value = .Cells(i, "B").Value
                ElseIf ws.Cells(targetrow, "B").Value <> .Cells(i, "B").Value Then
                
                    If ws.Cells(targetrow + 1, "A").Value <> .Cells(i, "A").Value Or _
                        ws.Cells(targetrow + 1, "B").Value <> .Cells(i, "B").Value Then
                        
                        targetrow = targetrow + 1
                        ws.Rows(targetrow).Insert
                        ws.Cells(targetrow, "A").Value = .Cells(i, "A").Value
                        ws.Cells(targetrow, "B").Value = .Cells(i, "B").Value
                    End If
                End If
                
                basecol = 0
                On Error Resume Next
                basecol = Application.Match(.Cells(i, "E").Value, ws.Rows(1), 0)
                On Error GoTo 0
                If basecol > 0 Then
                
                    ws.Cells(targetrow, basecol + .Cells(i, "D").Value - 1).Value = .Cells(i, "C").Value
                End If
            Next i
        End With
        
        Application.ScreenUpdating = True
    End Sub

  6. #6

    Solved:VBA code to insert the qty to the respective cell.

    Thanks Bob, Thanks a lot

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •