[VBA] .formula = "=" & variable - Error 1004

[Uehara]

Hello,

I'm trying to use the .formula function in VBA but I don't know how to use it with a variable following the "=" character...

Here's my code:

Dim sheetlist As String
Dim i As Integer

i = 2

While Range("B" & i).Value <> ""

   sheetlist = Range("B" & i).Value
   Range("C" & i).Formula = "=" & sheetlist & " '!$D$3

   i = i + 1

Wend

I got a "error 1004 : Application-defined or object-defined error".

Thanks

 

[bjurney]

Almost got it, just put a ' after the = sign so it looks like this:

Dim sheetlist As String
Dim i As Integer
i = 2
While Range("B" & i).Value <> ""
   sheetlist = Range("B" & i).Value
   Range("C" & i) = "='" & sheetlist & "'!$D$3"
   i = i + 1
Wend

 

[Uehara]

Almost got it, just put a ' after the = sign so it looks like this:

Dim sheetlist As String
Dim i As Integer
i = 2
While Range("B" & i).Value <> ""
   sheetlist = Range("B" & i).Value
   Range("C" & i) = "='" & sheetlist & "'!$D$3"
   i = i + 1
Wend

Thank you bjurney, it works.

Got stuck 2 hours on this