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?

 

[texasalynn]

do a macro recorder, then you can edit the formula to remove the file reference

 

[andyinla]

So, if I remove the file reference in the macro I am going to create how does the macro find the source data?

 

[texasalynn]

you didn't say in your original post, so how do you? LOL

No seriously now what is the whole question? You want the formula to include the file or not?

 

[nbrcrunch]

some things you just have to have confidence in yourself and try it first. Then, afterwards post back with questions.

However, in your situation, you reference an external file. How will your macro work if you remove that reference? You need to clarify exactly what is being removed and what you are trying to accomplish.

 

[andyinla]

Well, the macro has to know where the source data is...so with that in mind, how do I incorportate that into the macro?

Thank you

 

[nbrcrunch]

you're still not being speicific. What is the "source"? The external file?

so you're saying that K626 is what you want to make a variable entry?

=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))

 

[andyinla]

the source is the external file...

so what the formula does is checks cell K241 for example, takes that number and compares it with the invoice numbers in source file, if there is a match it populates the yr and month into a different cell in cost report, not the source file, the document where the data is populated into...

 

[nbrcrunch]

Ok, I was going to recommend a UDF, but you really don't want a formula at all. You want a VBA solution so that the result is a static value.

 

[andyinla]

Thanks for the reply...but I have no idea what that means.