how to add a Formula in VBA

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
farmingtonisAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
SimonConnect With a Mentor Commented:
You need to set the .Formula property rather than the .value property.

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

But, you still have some work to do, because you probably need to refer to the current row in the formula rather than "Q2".

You can do this by inserting your i value into the formula for each row instead of the static value "2".
Cells(i, "B").Formula = "=IFERROR(INDEX(SHEET1!A:C,MATCH(Q" & i & ",SHEET1!A:A,0)+COUNTIF(SHEET1!A:A,Q" & i & ")-1,2),"")"

The above should work. Others may post a faster method though... thinking of filtering the range for non-empty values first and filling down?
0
 
Rgonzo1971Connect With a Mentor Commented:
Instead of a loop

pls try the " char inside a string must be doubled see formula

Cells(2, "B").Formula = "=IFERROR(INDEX(SHEET1!A:C,MATCH(Q2,SHEET1!A:A,0)+COUNTIF(SHEET1!A:A,Q2)-1,2),"""")"
Range(Cells(2, "B"), Cells(LastRow, "B")).FillDown

Regards
0
 
SimonCommented:
I didn't actually test the code yesterday. Yes, it does need doubled quote marks within the formula.

The loop method is slower than fill down, but more configurable (if you only want the formula on lines where a particular condition exists).
0
All Courses

From novice to tech pro — start learning today.