asked on

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!

ASKER CERTIFIED SOLUTION

Saqib Husain

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Shanan212

ASKER

Thanks! That would work but how would I keep the formula dynamic (according to changing rows in the for loop)?

Saqib Husain

change F2 to

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

If you need C2 to vary as well change it to

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

TinTombStone

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')"

SOLUTION

Norie