Convert Formula to Macro...

[andyinla]

I want to convert the formula below to a macro so there is not a file reference as listed in the formula.

=IF(ISBLANK(K626) = TRUE, "", VLOOKUP(CONCATENATE("", K626),'C:\Documents and Settings\ahaspel\Desktop\[Copy of Andrew Haspel (4).xls]Job Cost Detail Report 3'!$G$1:$K$1000,5,FALSE))

Any ideas of how?

 

[Airwolf]

I think if you turn on macro recorder then record entering your formula then when complete and entered do Ctrl+c with the cursor in the same cell. Then Alt e..s..v then stop you recorder. You should have a macro that fills a cell with the formula, then overwrites the formula with its current value.

Will that float your boat?

 

[andyinla]

Ok here is what I did for the macro:

ActiveCell.FormulaR1C1 = _
"=IF(ISBLANK(RC[-2]) = TRUE, """", VLOOKUP(CONCATENATE("""", RC[-2]),'C:\Documents and Settings\ahaspel\Desktop\[Copy of Andrew Haspel (4).xls]Job Cost Detail Report 3'!R1C7:R1000C11,5,FALSE))"
End Sub


Any suggestions to that?

Do I have to edit the macro when the source files changes?
I would imagine I do...