I am trying to have a cell refer to a work book which can be filled and
cleared with a macro. What i am trying to do is have the path to the
other workbook in a cell.

This is what i have now:

Range("D3").FormulaR1C1 = _
"=IF(RC[-2]='C:\GLEXCEL\" & [H4] &
"LEDGER'!RC[-2],'C:\GLEXCEL\" & [H4] & "LEDGER'!RC[2],0)"
Range("D3").AutoFill Destination:=Range("D3:D123"),
Type:=xlFillDefault
Calculate

Now i want to change that so i have to put the full path in H4 and have
the code just look there but when ever i try that it does not work.

Any ideas anyone?

Thanks,
Arthur

Hi Leon,

You can't use VBA syntax in cell formulas! Try this simple solution:

In D3 enter =IF($H4<"","C:\GLEXCEL\"&$H4,"")

which will concatenate your path to whatever value is in H4 if it's not
empty. If H4 is empty then it leaves the cell blank. (as in empty string)

This assumes H4 holds the value that you want placed in D3. If you fill
down, the next row (D4) will receive the path + the value in H5, ..and so on.
Is this what you want?

HTH
Regards,
GS

"Leon" wrote:

I am trying to have a cell refer to a work book which can be filled and
cleared with a macro. What i am trying to do is have the path to the
other workbook in a cell.

This is what i have now:

Range("D3").FormulaR1C1 = _
"=IF(RC[-2]='C:\GLEXCEL\" & [H4] &
"LEDGER'!RC[-2],'C:\GLEXCEL\" & [H4] & "LEDGER'!RC[2],0)"
Range("D3").AutoFill Destination:=Range("D3:D123"),
Type:=xlFillDefault
Calculate

Now i want to change that so i have to put the full path in H4 and have
the code just look there but when ever i try that it does not work.

Any ideas anyone?

Thanks,
Arthur