Loop through a dynamic range and apply code in a seperate sheet

[DaveGib]

Hi There,
I appreciate any help I can get here...
I need to loop through a dynamic range on a sheet called Lists, column AB and place the contents in a sheet called Reports, starting at position B10
I then need to apply a series of formulas to that row.
This process must be repeated for each item in the range on sheet Lists.
I have recorded a macro (below) for the formulas that I want to put in the row. I have commented out the formulas I used in Excel.
Also, if it is not too difficult would it be possible to make reading the range on Sheet HOME dynamic? I have hard coded a number of 100,000 rows, but it would be ideal to be dynamic.
If anyone can help I would be grateful
Many thanks
Dave

Sub WIP()
'
 
' WIP Macro
'
'
    Range("B10").Select
    ActiveCell.FormulaR1C1 = "=Lists!R[-7]C[26]"  '  Get Material Description
                        '     =Lists!AB3
    Range("C10").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMIF(HOME!R[23]C[-2]:R100000C[-2],RC[-1],HOME!R33C[-1]:R100000C[-1])"  ' Get total stock issued
        '=SUMIF(HOMEA$33:A#100000,B10,HOME!B$33:B$100000)
    Range("D10").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMIFS(HOME!R33C[-2]:R100000C[-2],HOME!R33C[7]:R100000C[7],""COMPLETE"",HOME!R33C[-3]:R100000C[-3],RC[-2])" ' Get Stock used on Completed Houses
        '=SUMIFS(HOME!B$33:B$100000,HOME!K$33:K$100000,"COMPLETE",HOME!A$33:A$100000,B10)
    Range("F10").Select
    ActiveCell.FormulaR1C1 = "=RC[-3]-RC[-2]"  ' Calculate The WIP Stock
                        '     =C10-D10
    Range("G10").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5],Stock,3,FALSE)"  ' Get Current Price of Stock item
                        '     =VLOOKUP(B10,Stock,3,FALSE)
    Range("G10").Select
    Selection.NumberFormat = "#,##0.00"
    Range("H10").Select
    ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]"  ' Calculate value of WIP stock
                        '     =F10*G10
    Range("H10").Select
    Selection.NumberFormat = "#,##0.00"
End Sub

[D_Marcel]

Hi DaveGib! If you want to perform this action several times, the macro recorder will not be helpful, once it never inserts in the code, loop structures such as Do While, Do Until or For Each Next. Can you please post a sample with dummy data of your spreadsheet? I would like to take a look.

Regards!

[DaveGib]

Hi Marcel,
Thanks Sooo much for offering to have a look at this.

I have attached a dummy of what I am trying to do, but basically I need to Copy a range of items in column AB in the worksheet "Lists" into the Worksheet "Reports", starting at position B10 and insert the series of formula for each row that I recorded, for each item.

Many thanks and
Regards
Dave

[D_Marcel]

Dave, sorry for the delay. I understand that you need to copy the informations of the columns A and B of the sheet 'Lists' in the sheet 'Reports' starting at cell B10. And then insert in the cells C10, D10, F10, G10 and H10 the formula above. But there are two points:

In Column AB, you have two informations, 'Name' and 'No'

Shezi LM	312
Gumedi P	324
Dlamini S	613

Will you concatenate this information in B10 of the sheet 'Reports'?
If possible, send an example of how the information should be displayed in the B10.

Thanks!

[Bob Phillips]

Sub WIP()
Dim lastrow As Long
Dim numrows As Long

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    With Worksheets("Reports")
    
        numrows = Worksheets("Lists").Cells(.Rows.Count, "AB").End(xlUp).Row
        lastrow = Worksheets("Home").Cells(.Rows.Count, "BB").End(xlUp).Row
        .Range("B11").Resize(numrows).FormulaR1C1 = "=Lists!R[-7]C[26]" '  Get Material Description
        .Range("C11").Resize(numrows).FormulaR1C1 = _
                "=SUMIF(HOME!R[23]C[-2]:R100000C[-2],RC[-1],HOME!R33C[-1]:R" & lastrow & "C[-1])" ' Get total stock issued
        .Range("D11").Resize(numrows).FormulaR1C1 = _
                "=SUMIFS(HOME!R33C[-2]:R100000C[-2],HOME!R33C[7]:R" & lastrow & "C[7],""COMPLETE"",HOME!R33C[-3]:R" & lastrow & "C[-3],RC[-2])" ' Get Stock used on Completed Houses
        .Range("F11").Resize(numrows).FormulaR1C1 = "=RC[-3]-RC[-2]" ' Calculate The WIP Stock
        .Range("G11").Resize(numrows).FormulaR1C1 = "=VLOOKUP(RC[-5],Stock,3,FALSE)" ' Get Current Price of Stock item
        .Range("H11").Resize(numrows).FormulaR1C1 = "=RC[-2]*RC[-1]" ' Calculate value of WIP stock
        .Range("G11").Resize(numrows, 2).NumberFormat = "#,##0.00"
    End With

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

[DaveGib]

Marcel, - Thanks for getting back to me, I think our time zones are abt 12 hrs apart, when you are awake, I am asleep and vice versa!

xld, - You are a GENIUS! - you make it look so simple! Your solution works perfectly - THANK YOU very much!! The code is really neat, - I had been reading up on how to use .Resize, but couldn't get the syntax right.

thank you both for taking the time to help me..........
Dave