• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 394
  • Last Modified:

Assigning vlookup formula using vba

Set BaseWks = Sheets("Summary Data")
Set PivotWks = Sheets("PivotTab")
lastrow = PivotWks.UsedRange.Rows.Count - PivotWks.UsedRange.Row

For n = 2 To lastrow
BaseWks.Cells(n, 5).Formula = (BaseWks.Cells(n, 6).Value) / (WorksheetFunction.VLookup(BaseWks.Cells(n, 3), PivotWks.Range("A2:B" & lastrow), 2, False))
Next n

Hi, I developed the above formula (so far) in an attempt to covert the excel formula to vba

=IF(VLOOKUP(C2,PivotTab!A2:B95,2,FALSE)<>"",F2/VLOOKUP(C2,PivotTab!A2:B95,2,FALSE),"No Data")

Open in new window

As you can see, I've figured out the vlookup part (it works) and the division part. But putting the formula with 'IF' statement and as a live formula is been a problem so far.

When I say 'live' I want the formula to be there in the excel cell instead of just the value-result being displayed (which is whats happening now)

Any help is much appreciated!
2 Solutions
Saqib Husain, SyedEngineerCommented:
You are mixing two things. The left of the = sign is looking for a formula whereas the right side of the = is returning only the value.

To return you need to assign the Excel syntax within quotes...

BaseWks.Cells(n, 5).Formula = "=IF(VLOOKUP(C2,PivotTab!A2:B95,2,FALSE)<>"""",F2/VLOOKUP(C2,PivotTab!A2:B95,2,FALSE),""No Data"")"
Shanan212Author Commented:
Thanks! That would work but how would I keep the formula dynamic (according to changing rows in the for loop)?

Saqib Husain, SyedEngineerCommented:
change F2 to

" & cells(n,6).address & "

If you need C2 to vary as well change it to

" & cells(n,2).address & "
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Something like

BaseWks.Cells(n, 5).Formula = "=IF(VLOOKUP(C" & n & ",PivotTab!A2:B95,2,FALSE)<>'',F2/VLOOKUP(C" & n & ",PivotTab!A2:B95,2,FALSE),'No Data')"
NorieData ProcessorCommented:
Try this.
LookupRange = "'" & PivotWks.Name & "'!A2:B" & lastRow

BaseWks.Range("E2:E" & lastRow).Formula = "=IF(VLOOKUP(C2, " & LookupRange & ", 2,0) ="""",""No Data"", F2/VLOOKUP(C2," & LookupRange & ", 2,0))"

Open in new window

Shanan212Author Commented:
Thanks all!

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now