DaveGib
01-12-2014, 09:24 AM
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
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