formula in VBA

[Dave F]

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

 

[Dave Peterson]

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.

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

 

[Dave F]

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.

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.

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