CODEWITHSUNDEEP
excelvba
Chuck
Chuck

Reputation: 1

Update row/cell references while looping

I am referencing cells in another workbook to copy cell contents in that workbook. I want to loop through the rows in that workbook until there is a blank row. Example:

Range("B13:C13").Select
ActiveCell.FormulaR1C1 = "='[Junk Work File.xlsx]Total'!R1C8"

Is there a way that I can update the row/column (R1C8) so that I pick up the cell contents of the subsequent rows?

Upvotes: 0

Views: 81

Answers (1)

nwhaught
nwhaught

Reputation: 1592

Because the formula is a string, you can declare a variable, increment it, convert it to a string, and incorporate it into your formula. 

Dim i as Integer
i = 1
Do While [other workbook row is not blank] 'put your loop condition here
    ActiveCell.FormulaR1C1 = "='[Junk Work File.xlsx]Total'!R" & CStr(i) & "C8"
    i = i + 1
Loop

Upvotes: 1

Related Questions