Dynamic cell reference error in VBA

[hannahcw]

I'm fairly new to VBA, so forgive my obvious beginner's errors. I'm looking to create a macro that will return a formula reference to a cell in another sheet. I have the below code. It almost does what I want, but it returns the following: =@'Jobs and pricing info'!'E9' and the next one =@'Jobs and pricing info'!'E10' and so on. I don't understand why there's an @ at the beginning and why it puts the single quotes around the E9. Please help!

Sub LinkCells()

Sheets("Direct Labor").Select
Range("E122").Select
ActiveCell.FormulaR1C1 = "='Jobs and pricing info'!E9"
ActiveCell.Offset(145).Select

Dim x As Integer
For x = 1 To 199
ActiveCell.FormulaR1C1 = "='Jobs and pricing info'!E" & (9 + x)
ActiveCell.Offset(145).Select
Next x
End Sub

 

[HaHoBe]

Hi hannahcw,

maybe

Sub LinkCells()
'https://www.mrexcel.com/board/threads/dynamic-cell-reference-error-in-vba.1230493/Dim lngCnt As Long

For lngCnt = 0 To 199
  Sheets("Direct Labor").Cells(122 + lngCnt * 145, "E").Formula = "='Jobs and pricing info'!E" & 9 + lngCnt
Next lngCnt

End Sub

Ciao,
Holger

 

[hannahcw]

Hi hannahcw,

maybe

Sub LinkCells()
'https://www.mrexcel.com/board/threads/dynamic-cell-reference-error-in-vba.1230493/Dim lngCnt As Long

For lngCnt = 0 To 199
  Sheets("Direct Labor").Cells(122 + lngCnt * 145, "E").Formula = "='Jobs and pricing info'!E" & 9 + lngCnt
Next lngCnt

End Sub

Ciao,
Holger

That is much shorter than what I had and I'm not sure I entirely understand how it works, but it does. I'll read up on it. Thanks so much!

 

[HaHoBe]

Hi Hannah,

the code just uses one loop and avoids moving the cursor on the sheet. The worksheet where the links are inserted needs not to be the active one as it is referenced. And I substituted FormulaR1C1 which would expect the formula to be in R1C1-way with Formula which expects the A1-way you used.

So the code starts on Row 122 with the link row 9 and counter 0, it adds the counter to the link row and the result of the multiplication of counter * 145 to the starting row - that's all.

Holger