recording macro for a formula

[timmulla]

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,4,FALSE)),VLOOKUP(TRIM(A2),Sheet1!A:C,3,FALSE),(VLOOKUP(TRIM(A2),Sheet3!A,4,FALSE)))),"WE
DON'T HAVE A START DATE IN OUR
SYSTEMS",IF(ISERROR(VLOOKUP(TRIM(A2),Sheet3!A,4,FALSE)),VLOOKUP(TRIM(A2),Sheet1!A:C,3,FALSE),VLOOKUP(TRIM(A2),Sheet3!A,4,FALSE)))

 

[ward376]

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

 

[ward376]

Here's a workaround:
(check line wrapping)
ActiveCell.Formula = _
"=IF(ISNA(IF(ISNA(VLOOKUP(B1,Sheet1!A:C,3,FALSE))" & _
",VLOOKUP(B1,Sheet3!A,4,FALSE),(VLOOKUP(B1,Sheet1" & _
"!A:C,3,FALSE)))),""text"",(IF(ISNA(VLOOKUP(B1,Sheet1!" & _
"A:C,3,FALSE)),VLOOKUP(B1,Sheet3!A,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

 

[timmulla]

Got it to work thanks