Convert Formula to Macro...

andyinla

New Member
Joined
Mar 19, 2009
Messages
10
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?
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
So, if I remove the file reference in the macro I am going to create how does the macro find the source data?
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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))
 
Upvote 0
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...
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,485
Messages
6,113,931
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top