PDA

View Full Version : Solved: Use Vlookup in VBA Code



austenr
07-23-2010, 10:37 AM
I want to use a worksheet change event that will execute certain vlookups only when a certain condition in a cell is met. I want to do this because I do not know of any other way to keep the formulas from being wiped out as I will be setting the cells I currently have the lookups in to zero in some instances hence wiping out the formula.

Can someone provide an example?

bkgashok
07-23-2010, 11:07 AM
........the lookups in to zero in some instances........


try to convert the 'some instances' to <condition> and use vlookup() in if(<condition>,vlookup(),0) or if(<condition>,0,vlookup())

austenr
07-23-2010, 11:17 AM
I got this far but it doesnt like the "1" in the formula. I get an "expected end of statement" error on compile.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Sheets("CP1").Select

If Range("D12").Value = "X" Then
Range("E25").formula = "=IF(TRIM(MEDCVG)="1",VLOOKUP(YearsOfService,Indemnity,IF(Age<=65,7,14),FALSE),0)/12"
End If
End Sub

austenr
07-23-2010, 11:38 AM
Fixed it. Needed double quotes around the 1.

mdmackillop
07-24-2010, 01:34 AM
Hi Austen
My tip for complicated (or simple) quotes.
Put your formula in the worksheet, delete the = sign, record a macro inserting the = and you should get a working VBA formula.