I've created the following formula on a worksheet that works properly but
when I try to record a macro to create the formula in visual basic editor it
doesn't would. I get an error "unable to record". Can anyone help me with
this?

=IF(ISERROR(IF(ISERROR(VLOOKUP(TRIM(A2),Sheet3!A:D ,4,FALSE)),VLOOKUP(TRIM(A2),Sheet1!A:C,3,FALSE),(V LOOKUP(TRIM(A2),Sheet3!A:D,4,FALSE)))),"WE
DON'T HAVE A START DATE IN OUR
SYSTEMS",IF(ISERROR(VLOOKUP(TRIM(A2),Sheet3!A:D,4, FALSE)),VLOOKUP(TRIM(A2),Sheet1!A:C,3,FALSE),VLOOK UP(TRIM(A2),Sheet3!A:D,4,FALSE)))
--
Regards,

timmulla

Possibly too many characters in a cell (310)... formulas are strings
in vba.

Let me make sure I understand - you want to look for a value
corresponding to the value in a2 in column c on sheet 1 or column d on
sheet 3 and if it's not found enter text?

Cliff Edwards

Here's a workaround:
(check line wrapping)
ActiveCell.Formula = _
"=IF(ISNA(IF(ISNA(VLOOKUP(B1,Sheet1!A:C,3,FALS E))" & _
",VLOOKUP(B1,Sheet3!A:D,4,FALSE),(VLOOKUP(B1,Sheet 1" & _
"!A:C,3,FALSE)))),""text"",(IF(ISNA(VLOOKUP(B1,She et1!" & _
"A:C,3,FALSE)),VLOOKUP(B1,Sheet3!A:D,4,FALSE), " & _
"(VLOOKUP(B1,Sheet1!A:C,3,FALSE)))))"

I recorded entering the text in a cell then added the "=" in the vb
editor: note "ActiveCell.Formula =", not "ActiveCell.FormulaR1C1 =".
Edit the references and text to your needs.

Not really sure the mechanics behind this issue - anyone else?


Cliff Edwards

Got it to work thanks
--
Regards,

timmulla


"ward376" wrote:

Here's a workaround:
(check line wrapping)
ActiveCell.Formula = _
"=IF(ISNA(IF(ISNA(VLOOKUP(B1,Sheet1!A:C,3,FALS E))" & _
",VLOOKUP(B1,Sheet3!A:D,4,FALSE),(VLOOKUP(B1,Sheet 1" & _
"!A:C,3,FALSE)))),""text"",(IF(ISNA(VLOOKUP(B1,She et1!" & _
"A:C,3,FALSE)),VLOOKUP(B1,Sheet3!A:D,4,FALSE), " & _
"(VLOOKUP(B1,Sheet1!A:C,3,FALSE)))))"

I recorded entering the text in a cell then added the "=" in the vb
editor: note "ActiveCell.Formula =", not "ActiveCell.FormulaR1C1 =".
Edit the references and text to your needs.

Not really sure the mechanics behind this issue - anyone else?


Cliff Edwards