Solved: Use Vlookup in VBA Code

[austenr]

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]

........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]

I got this far but it doesnt like the "1" in the formula. I get an "expected end of statement" error on compile.

[VBA]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),FAL SE),0)/12"
End If
End Sub
[/VBA]

[austenr]

Fixed it. Needed double quotes around the 1.

[mdmackillop]

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.