Variable reference in macro formula

[Big Tony]

Range("B7").Select
ActiveCell.FormulaR1C1 = "=[454679845456.xls]Promoforma!R6C5"

This is my current formula in VB where I am trying to create a formula in one file linking to another. In the above, the fomula is extracting info from the 454679845456.xls file into a summary book. Is there a way that I can have the "454679845456.xls" portion of the formula as a variable? Possibly pulling from whatever other file is open at the time as it won't always be called 454679845456.xls.

I hope this makes sense.

At the moment i have a macro set-up where one file is 'saved as' based on a number inputed in one box. in the above, they have have inputted the number 454679845456. the next step i am trying to achieve is to have this number and the data on this new sheet exported to a summary page. at the moment my formula will keep pulling from the 454679845456.xls file as i don't know how to have this as a variable; pulling in the data from the current open book might be an option but if they more than one file open then this may not work.

appreciate any assistance you can give.

[shg]

Welcome to the forum.

    Dim s       As String

    s = "454679845456.xls"
    Range("B7").FormulaR1C1 = "=[" & s & "]Promoforma!R6C5"

Please read the Forum Rules and then edit your post to wrap your code with Code Tags.

[Big Tony]

Welcome to the forum.

    Dim s       As String

    s = "454679845456.xls"
    Range("B7").FormulaR1C1 = "=[" & s & "]Promoforma!R6C5"

Sorry, not sure I get this. Will this allow me to change the S value depending what is in a specific cell? Have run this and it always seems to reference to the 454679845456.xls file. What I need is S to be variable and change depending on what is in cell C7. Would just use an index formula but this doesn't work when the other file is closed.

Maybe I can change s = to a range. is that possible? Sorry for the noob questions.

Please help... Cheers

[shg]

The example just demonstrates that you can use a variable. You can assign the value of s from the contents of a cell in some open workbook,

s = Workbooks("myWorkbook.xls").Worksheets("Sheet1").Range("A1").Value

...or any other way you like.