VBA inserts ' into formula

0
votes

I'm sure this is probably the most simple thing ever, but I'm really confused by this.

I have the following code in VBA:

For j = 0 To n
    For i = 0 To 11
        Dim y As Integer
        y = Sheets("Checking").Cells(3 + j, 4 + (i * 4)).Value
        Sheets("Checking").Cells(3 + j, 5 + (i * 4)).FormulaR1C1 = "=VLookup(A" & 3 + j & ",PPG," & y + 1 & ",0)"
    Next i
Next j

This does what I need it to do, however when it inserts the VLookup formula into excel it comes out as =VLOOKUP('A3',PPG,2,0) with single quotes around the cell reference.

Why is it doing this, and how can I correct it?

2
excelvba

2 Answers

1
votes

Use .Formula rather than .FormulaR1C1. The latter exhibits the behaviour you're observing by design.

0
votes

I'd prefer to avoid the worksheet at all since you are using VBA:

Sub M_snb()
 sn = [PPG]
 sp = [PPG].Columns(1)

 For j = 0 To n
  For jj = 0 To 11
    With Sheets("Checking").Cells(3 + j, 5 + (jj * 4))
       .Value = sn(Application.Match(Cells(j + 3, 1), sp, 0), .Offset(, -1).Value)
    End With
  Next
 Next
End Sub