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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SimonCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rgonzo1971Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.