formula in VBA
That's what I ended up doing, thanks. For whatever reason when I copied the
formula from XL to VBE the filepath didn't seem to copy as well, so I've
manually entered them, and now it works.
As for the bracket I was missing--yes, I was editing the formula in the VBE
and neglected to put that back in there.
Thanks,
Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.
"Dave Peterson" wrote:
Untested.
Why not just include the path in the code:
Range("T2").FormulaR1C1 = "IF(AND(ISNUMBER(MATCH(LEFT(RC[-18],3)," _
& "'c:\my documents\excel\" _
& "[ELR expense account identification.xls]Sheet1'!R2C1:R12C1,0))," _
& "ISNUMBER(MATCH(RC[-17]," _
& "'c:\my documents\excel\" _
& "[Frank''s expense codes--GDCS and " _
& "non-GDCS.xls]sheet1'!R2C1:R39C1,0))),""Extract"","""")"
And watch your brackets. You forgot to use the right square bracket in this
portion:
....(left(rc[-18,3), ....
And if your worksheet name contains an apostrophe, you're going to have to
double it up.
For example: Frank''s, not Frank's.
===
But I'm not quite sure what workbooks own those worksheets.
Dave F wrote:
I have a module, in which one step puts the following formula in T2:
Range("T2").Select
ActiveCell.FormulaR1C1 = "=IF(AND(ISNUMBER(MATCH(LEFT(RC[-18,3),'[ELR
expense account
identification.xls]Sheet1'!R2C1:R12C1,0)),ISNUMBER(MATCH(RC[-17],'[Frank's
expense codes--GDCS and
non-GDCS.xls]Sheet1'!R2C1:R39C1,0))),""Extract"","""")"
The problem is, if I copy this module to a new workbook the link to the
external workbooks is broken. My vision is that I can use this module in any
workbook and the formula above will automatically retain the file path to
these workbooks (which filtepath will never change).
How do I get the filepath to appear in the formula?
Thanks,
Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.
--
Dave Peterson
|