VBA code to insert the qty to the respective cell.

S

sanmaya

New member
Joined
Jul 28, 2014
Messages
4
Reaction score
0
Points
0
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 .
 

Attachments

  • Sample data file.xlsx
    19.1 KB · Views: 6
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
 
Bob Phillips said:
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
Click to expand...


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?
 
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.
 

Attachments

  • Samdata.xlsx
    18.1 KB · Views: 8
Last edited by a moderator:
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
 
Solved:VBA code to insert the qty to the respective cell.

Thanks Bob, Thanks a lot
 
You must log in or register to reply here.
Back
Top