farmingtonis
asked on
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,S HEET1!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)+COUNTI F(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
=IFERROR(INDEX(SHEET1!A:C,
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
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,
That didn't work either. I am not good at the formula thing in VBA.
Thanks in advance
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The loop method is slower than fill down, but more configurable (if you only want the formula on lines where a particular condition exists).