Macro - how to change a cell reference in a loop.

[andy-s]

Could anyone please help me with the macro below - look for "THIS IS MY PROBLEM"

Sub Transfer()

Dim LRD As Long     'last row datasheet

Set DS = Sheets("Rawdata")

LRD = DS.Range("C8:C" & Rows.Count).Find("*", DS.[C8], xlFormulas, xlPart, xlByRows, xlPrevious, False, False).Row

For Counter = 9 To LRD

    'THIS IS MY PROBLEM – Every time Counter change, the cell reference R[9]C[24] should change as well. When Counter change to 10, the cell reference should change to R[10]C[24] and so on. How do I do this?
    Sheets("Processing").Select
    Range("A1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "='Rawdata'!R[9]C[24]"
  
    'Copy data from Processing to Rawdata
    With Sheets("Rawdata")
    NxRow = .Cells(65536, 28).End(xlUp).Row + 1
    .Cells(NxRow, 28).Value = Sheets("Processing").Range("A29:A29").Value
    End With
    
Next Counter

End Sub

 

[RalphA]

Try changing your line,
ActiveCell.FormulaR1C1 = "='Rawdata'!R[9]C[24]"
to
ActiveCell.FormulaR1C1 = "='Rawdata'!R[Counter]C[24]"

 

[erik.van.geit]

Hello, andy-s,

try this
ActiveCell.FormulaR1C1 = "='Rawdata'!R[" & Counter & "]C[24]"

kind regards,
Erik

 

[andy-s]

Thx a lot

andy-s