Save variable into a cell to use in a formula

[johnnyL]

Need some help here. ;( Hopefully someone here can help.

I have googled many other questions I have had and several of the results lead me to this forum. So I figure this would be a good place to post my question.

I have excel 2007 and I am new to this Excel stuff ... been playing with it for about 1.5 months or so, so I am still a newbie so to speak in Excel.
Here is a brief description of what I am trying to do:

I am working with two workbooks, 'Completed.xlsx' & 'Next.xlsx', for example. What I want to do is have the user select the 'completed.xlsx' workbook, whatever existing workbook they want to choose, and then save that chosen workbook name into a cell into the other open workbook 'Next.xlsx', or whatever they called it.

The checking for existing or already open workbooks is working fine, so that part is not an issue. I can get both workbooks to open, if one or both are not already open, so that is not a problem.

Basically, the only problem I am faced with now, is how to save the workbook chosen ('completed.xlsx') into a cell that is located in the 'Next.xlsx' workbook. The names of the workbooks may change because I am asking the user for the names of both workbooks. So I can't 'hardcode' the name of the workbook into the 'Next.xlsx' workbook cell, for example.
So, in a nutshell, I will ask the user to select two different workbooks, names will vary, and I want to save the name of the first selected workbook into a cell of the second selected workbook, ie. save it as a variable.

I have tried various things, but I believe I have the syntax incorrect and therefor have been unsuccessful thus far in this goal. After it is saved into a cell in the second workbook, I want to use that cell value, ie. the first workbook name, to load a cell value from that first workbook name/sheet into the second workbook selected/sheet. Sheet names might vary between the workbooks, but the order of the sheets should be presumed to be in the same order. So basically both workbooks are arranged in the same order sheet wise, as well as the rows and columns.

I think I have explained my intentions, but if there is anything I am being vague about, please let me know, and I will offer up further info.

Any Help would be most appreciated! Thank you!

 

[Jerry Sullivan]

Welcome to the Board!

It's clear that you don't want to hard code the name of the workbooks into your VBA code because the user needs to select the two workbooks.

What isn't clear is once the user selects the two workbooks, can the name of the first selected workbook be placed in the cell of the second workbook as a Value, or does it need to be a dynamic reference to the name of the second workbook?

If you post your code (or at least a snippet before and after the user selects the two workbooks), that will help.

 

[johnnyL]

Welcome to the Board!

TY so much for the welcoming JS411!

It's clear that you don't want to hard code the name of the workbooks into your VBA code because the user needs to select the two workbooks.

What isn't clear is once the user selects the two workbooks, can the name of the first selected workbook be placed in the cell of the second workbook as a Value, or does it need to be a dynamic reference to the name of the second workbook?

If you post your code (or at least a snippet before and after the user selects the two workbooks), that will help.

I would be happy to share any code that anyone wants/needs to look at to possibly help me out.

I am actually currently doing what you mentioned, placing the name of the first workbook into the second workbook, as you mentioned.

What I am doing right now, that works is, I have the hard coded name of the first workbook in a worksheet cell that is located in the second workbook. For example, Cell A2 in a worksheet of the second workbook reads completed.xlsx

In another cell in the second workbook there is a formula to add 25 to whatever value it finds in the first workbook 3rd worksheet, for example, and save the new result into the 3rd worksheet of the second workbook.

Sample of that current code

=INDIRECT("'[" & 'File Path'!A2 & "]Cycle C Day 1'!" & ADDRESS(ROW(), COLUMN() + 12)) + 25

File Path = worksheet name from the second workbook.
A2 = the cell number from that worksheet ... these two tell where to look for the hard coded file name of the first workbook.

Cycle C Day 1 = this tells which worksheet to look at in the first workbook ... 3rd worksheet in this case.

ADDRESS(ROW(), COLUMN() + 12)) + 25 = Take the value from 12 columns to the right and add 25 to its value and store it back into the second workbook 3rd worksheet.

Like I said earlier, the worksheets could possibly vary in names between workbooks, one will most likely have a date added to it, but the order of the worksheets will stay the same between workbooks regardless of the name of the workbooks.

I have only been playing with excel for a little over a month so I still have much to learn. I have googled answers to my previous questions that I have had up to now, but this dilemna still remains an unsolved mystery to me.

Folks, lemme know if I need to provide any other info to assist me in my current dilemna. Thanks again folks!

 

[Jerry Sullivan]

When you wrote that you want the user to select two workbooks, I assumed you were describing a VBA macro. Your last post sounds like you are working with formulas only.
Do you have a preference to use or not use macros?

 

[johnnyL]

When you wrote that you want the user to select two workbooks, I assumed you were describing a VBA macro. Your last post sounds like you are working with formulas only.
Do you have a preference to use or not use macros?

Dang your quick, and perceptive too.

You are reading my mind it seems. LOL

I am using a macro to ask for the workbooks to use, but the formulas I mentioned are in the workbooks selected. So either way to solve my problem I am facing would be most apreciative. I am familiar with macro and cell formulas. Thank you!

 

[Jerry Sullivan]

Since you are already using macros to select the workbooks, I would try to use the VBA tools as much as possible.

This brings me back to the first question which was....

...can the name of the first selected workbook be placed in the cell of the second workbook as a Value, or does it need to be a dynamic reference to the name of the second workbook?

I'm not able to able to read your mind as you thought.

Also, please post the part of your code in which the user selects the two workbooks, so we can build on what you already have.

 

[johnnyL]

Since you are already using macros to select the workbooks, I would try to use the VBA tools as much as possible.

This brings me back to the first question which was....


I'm not able to able to read your mind as you thought.

Also, please post the part of your code in which the user selects the two workbooks, so we can build on what you already have.

Here is the code I use in the macro to ask for the workbook names ....

'
Get_Completed_File_Name:
'
    Dim Completed_File_Name  As String
'
' Show the 'Open' window and pass the selected file name to
' the String variable named "Completed_File_Name"
    Completed_File_Name = Application.GetOpenFilename _
        ("Excel Files (*.xl*), *.xl*,All Files(*.*),*.*", 1, _
        "Select the Current week file.      !!! Just an FYI !!! Hold down 'alt' key and press 'arrow up' key one time to go up one folder")

...

Get_Next_File_Name:
'
    Dim Next_File_Name As String
'
' Show the 'Open' window and pass the selected file name to
' the String variable named "Next_File_Name"
    Next_File_Name = Application.GetOpenFilename _
        ("Excel Files (*.xl*), *.xl*,All Files(*.*),*.*", 1, _
        "Select the Original Current Week File Name.      !!! Just an FYI !!! Hold down 'alt' key and press 'arrow up' key one time to go up one folder")

Hopefully that is what you are asking for. Thanks again!

 

[Jerry Sullivan]

Give this a try and see if it does what you want to do....

Sub DirectReferenceToWorkbook()
    Dim Completed_File_Name  As String
    Dim Next_File_Name As String
    
    Completed_File_Name = Application.GetOpenFilename _
        ("Excel Files (*.xl*), *.xl*,All Files(*.*),*.*", 1, _
        "Select the Current week file.")

    Workbooks.Open (Completed_File_Name)
    'remove file path
    Completed_File_Name = ActiveWorkbook.Name
    
    Next_File_Name = Application.GetOpenFilename _
        ("Excel Files (*.xl*), *.xl*,All Files(*.*),*.*", 1, _
        "Select the Next week file.")
    
    Workbooks.Open (Next_File_Name)
    Next_File_Name = ActiveWorkbook.Name
   
    With Workbooks(Next_File_Name)
    'this isn't needed if the workbook is directly referenced in Sheet3.
    ''' .Sheets(1).Range("A2") = Completed_File_Name
        
        .Sheets(3).Range("A2").FormulaR1C1 = _
            "='[" & Completed_File_Name & "]Cycle C Day 1'!" & "RC[12]+25"
    End With
End Sub

I've shown how you could place the Completed workbook name into Sheet1,
but that step is no longer needed since the formula in Sheet3 directly references
the Completed workbook.

The INDIRECT function is somewhat limiting because it doesn't work =
for references to closed workbooks, so hopefully this approach will be better for you.

 

[johnnyL]

Give this a try and see if it does what you want to do....

Sub DirectReferenceToWorkbook()
    Dim Completed_File_Name  As String
    Dim Next_File_Name As String
 
    Completed_File_Name = Application.GetOpenFilename _
        ("Excel Files (*.xl*), *.xl*,All Files(*.*),*.*", 1, _
        "Select the Current week file.")
 
    Workbooks.Open (Completed_File_Name)
    'remove file path
    Completed_File_Name = ActiveWorkbook.Name
 
    Next_File_Name = Application.GetOpenFilename _
        ("Excel Files (*.xl*), *.xl*,All Files(*.*),*.*", 1, _
        "Select the Next week file.")
 
    Workbooks.Open (Next_File_Name)
    Next_File_Name = ActiveWorkbook.Name
 
    With Workbooks(Next_File_Name)
    'this isn't needed if the workbook is directly referenced in Sheet3.
    ''' .Sheets(1).Range("A2") = Completed_File_Name
 
        .Sheets(3).Range("A2").FormulaR1C1 = _
           "='[" & Completed_File_Name & "]Cycle C Day 1'!" & "RC[12]+25"
    End With
End Sub

I've shown how you could place the Completed workbook name into Sheet1,
but that step is no longer needed since the formula in Sheet3 directly references
the Completed workbook.

The INDIRECT function is somewhat limiting because it doesn't work =
for references to closed workbooks, so hopefully this approach will be better for you.

Hey there again! I got a chance to test this and it gives an error 1004 undefined resonse or something like that.

The part you commented out works great ... the part highlighted in red was the part that errors out. I did notice a double equal sign at the beginning that I thought looked 'not right' as well as maybe missing an and symbol towards the end maybe? I dunno, I just started playing with Excel.

I messed with it a bit, but all I could get it to do was pretty much quote the whole line into a cell instead of making it a formula cell.

I also tried to incorporate a variable name for sheet names in the formula, but no luck thus far, I am gonna keep at it though.

 

[Jerry Sullivan]

Hi johnnyL,

It could be a problem with the name references not matching your situation, or with the formula that is being put in the cell.

Try placing this code in front of the part of the code that is giving the error.
Some text will be sent to the Immediate Window of your VBA editor.
The results should point us in the direction of the problem.

Debug.Print "Formula: " &  "='[" & Completed_File_Name & "]Cycle C Day 1'!" & "RC[12]+25"
Debug.Print "Next_File_Name: "  & Next_File_Name
Debug.Print "Sheet 3 name: " &  Workbooks(Next_File_Name).Sheets(3).name