Solved

# how to add a Formula in VBA

Posted on 2014-11-25

I have a spreadsheet that I want to add a formula to when i run some VBA. Basically, i want this formula to be entered when i run the code:

=IFERROR(INDEX(SHEET1!A:C,MATCH(Q2,SHEET1!A:A,0)+COUNTIF(SHEET1!A:A,Q2)-1,2),"")

I know how to write it manually, i just don't know how to do it via VBA.

I tried this:

Sheets("This Sheet").Select

Dim lastrow as Long

lastrow = [Q8000].End(xlUp).Row

For i = lastrow To 1 Step -1

If i = 1 then

GoTo Done

End If

If Cells(i, "Q").Value <> "" Then

Cells(i, "B").Value = "=IFERROR(INDEX(SHEET1!A:C,MATCH(Q2,SHEET1!A:A,0)+COUNTIF(SHEET1!A:A,Q2)-1,2),"")"

End If

Next i

Done :

As you probably know, that didn't work. I have the same problem with vlookup like this:

=vlookup(I2,sheet1!A:AP,5,False)

That didn't work either. I am not good at the formula thing in VBA.

Thanks in advance