vlookup - how to put this into VBA

[Melbridge]

This formula works in the cell but I need it in VBA and it doesn't want to
play!

The formula is in cell J4
=IF(I4=(VLOOKUP(K4,'E:\MANMON\TEAMS VALIDATION\Teams
Master\[TEAMS_Master.xls]Officers'!$A$2:$C$40,3,FALSE)),"","88888888")

I have tried this:
ActiveCell.FormulaR1C1 = "=IF(RC[-1]="(VLOOKUP(RC[+1],'E:\MANMON\TEAMS
VALIDATION\Teams
Master\[TEAMS_Master.xls]Officers'!$A$2:$C$40,3,FALSE)","""",88888888))"

Is it just a formatting thing?

 

[Bob Phillips]

You are mixing R1C1 and A1

Try

Const sVLOOKUP As String = _
"VLOOKUP(RC[+1],'E:\MANMON\TEAMS VALIDATION\Teams
Master\[TEAMS_Master.xls]Officers'!R2C1:R40C3,3,FALSE)"

ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]=" & sVLOOKUP & ","""",88888888)"

 

[Melbridge]

Thanks Bob,

I'll have to remember that - I'm not a programmer (as you probably guessed),
I just cut n paste then modify!

--
Regards, Dean

You are mixing R1C1 and A1

Try

Const sVLOOKUP As String = _
"VLOOKUP(RC[+1],'E:\MANMON\TEAMS VALIDATION\Teams
Master\[TEAMS_Master.xls]Officers'!R2C1:R40C3,3,FALSE)"

ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]=" & sVLOOKUP & ","""",88888888)"


--
__________________________________
HTH

Bob

This formula works in the cell but I need it in VBA and it doesn't want to
play!

The formula is in cell J4
=IF(I4=(VLOOKUP(K4,'E:\MANMON\TEAMS VALIDATION\Teams
Master\[TEAMS_Master.xls]Officers'!$A$2:$C$40,3,FALSE)),"","88888888")

I have tried this:
ActiveCell.FormulaR1C1 = "=IF(RC[-1]="(VLOOKUP(RC[+1],'E:\MANMON\TEAMS
VALIDATION\Teams
Master\[TEAMS_Master.xls]Officers'!$A$2:$C$40,3,FALSE)","""",88888888))"

Is it just a formatting thing?