PDA

View Full Version : [SOLVED] Loop through a dynamic range and apply code in a seperate sheet



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

D_Marcel
01-14-2014, 02:42 PM
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
01-15-2014, 07:47 AM
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
01-15-2014, 01:40 PM
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
01-15-2014, 02:59 PM
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
01-15-2014, 10:20 PM
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