Multiple workbook
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 |
Multiple workbook
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 |
All times are GMT +1. The time now is 06:50 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com