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]

Johnny, I need to sign off for a few hours.
The code works for me and thinking about this a bit more, the most likely scenario is a problem with the reference to Sheets(3).

Sheets(3) means the 3rd sheet in your workbook, which isn't the same as Sheets("Sheet3") or "Sheets(3)"

You would get a 1004 error if you only have 2 sheets in your workbook and try to reference Sheets(3), even if one of the sheets was called "Sheets3" or "Sheets(3)"

It's usually better to reference the Sheets by name, so you might want to change that reference regardless of whether or not the is the problem that was causing the error.

Please let me know what you find from the testing.

 

[johnnyL]

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

Thanks again! I found my error. It was the "Cycle C Day 1" part that was giving me the error. I was trying it on a Cycle B workbook / worksheet. Doh!

I did fix that problem and got it to run successfully! However, after seeing that it only did one cell, I now realize that I should have asked for the cell formula to use instead of macro code to use to do it. That was my mistake and I appologize. The reason I say that it would be easier with the cell formula approach is because each cell will have different values added to the original value received, ie. it won't always be + 25 for example. The looping required to do all those possibilities gave me a headache just thinking about it.

So if you get a chance, Maybe you could suggest a cell formula to use that would be more compatable than:

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

'File Path'!A2 = The worksheet and cell that I save the Completed_File_Name selected by the user. Thank you! that problem is now solved!

Cycle C Day 1 = The active worksheet that the formula is located in. This is what I have been trying to change to a variable that will work for any workbook that I happen to be in ... a sheet # for example would work, but I haven't figured out the correct syntax to use as of yet.

Please lemme know if any ideas occur.

I am not stuck on the Indirect command either, so if another way is easier for a cell formula, I am all ears.

 

[Jerry Sullivan]

... The reason I say that it would be easier with the cell formula approach is because each cell will have different values added to the original value received, ie. it won't always be + 25 for example.

Where does this different value added to the orginal value come from?
Does it already exist in a cell in one of the two workbooks that are opened?

Cycle C Day 1 = The active worksheet that the formula is located in. This is what I have been trying to change to a variable that will work for any workbook that I happen to be in ... a sheet # for example would work, but I haven't figured out the correct syntax to use as of yet.

That doesn't sound correct. In the example we started with, Cycle C Day 1 was the sheet in the Next workbook that the formula we were adding was referencing.

 

[johnnyL]

Where does this different value added to the orginal value come from?
Does it already exist in a cell in one of the two workbooks that are opened?

It varies depending on the cell and what that cell pertains to. The value added could be nothing, 10, 15, 25 in most cases.

That doesn't sound correct. In the example we started with, Cycle C Day 1 was the sheet in the Next workbook that the formula we were adding was referencing.

That hasn't changed. Maybe I didn't explain myself correctly. 'Cycle C Day 1' would be a worksheet in the 'next_File' workbook. All cell changes will be made to the 'Next workbook' based on values read in from the 'Completed_File' workbook.

So basically, the sheet names will be in the same order for both workbooks, they might vary in name though. The values from the 'completed_File' workbook are gonna be read and used to place in the 'Next' workbook.

Sorry for any confusion I may have created earlier.

 

[Jerry Sullivan]

It varies depending on the cell and what that cell pertains to. The value added could be nothing, 10, 15, 25 in most cases.

Yes, but how is the number determined? There has to be some pattern, relationship or lookup. Without a relationship, you won't be able to use a formula or VBA.

That hasn't changed. Maybe I didn't explain myself correctly. 'Cycle C Day 1' would be a worksheet in the 'next_File' workbook. All cell changes will be made to the 'Next workbook' based on values read in from the 'Completed_File' workbook.

My question stated it incorrectly. In the example we did 'Cycle C Day 1' was in the Completed workbook.
See your original formula:
=INDIRECT("'[" & 'File Path'!A2 & "]Cycle C Day 1'!" & ADDRESS(ROW(), COLUMN() + 12)) + 25

and my VBA code:
.Sheets(3).Range("A2").FormulaR1C1 = _
"='[" & Completed_File_Name & "]Cycle C Day 1'!" & "RC[12]+25"

 

[johnnyL]

Yes, but how is the number determined? There has to be some pattern, relationship or lookup. Without a relationship, you won't be able to use a formula or VBA.

Lemme give you some examples of the cell formulas I have now, This is from the E column in the 'Next_File':

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

And some of the cells in between there don't get anything added to them. Got a pattern for that?

My question stated it incorrectly. In the example we did 'Cycle C Day 1' was in the Completed workbook.
See your original formula:
=INDIRECT("'[" & 'File Path'!A2 & "]Cycle C Day 1'!" & ADDRESS(ROW(), COLUMN() + 12)) + 25

and my VBA code:
.Sheets(3).Range("A2").FormulaR1C1 = _
"='[" & Completed_File_Name & "]Cycle C Day 1'!" & "RC[12]+25"

Maybe I have that part mixed up, I told ya I just started messing with this Excel stuff. The worksheet name in my case, is normally gonna be the same, but doesn't have to be necesarily, so I guess the worksheet name represented there = the completed workbook worksheet name. Sorry about that.

 

[Jerry Sullivan]

I think our miscommunication on this relates to what is setup in advance of the user running the macro.

Your current process is to have several formulas set up inside the Next workbook, like those you showed....
=INDIRECT("'[" & 'Current Cycle File Path'!A2 & "]Cycle C Day 1'!" & ADDRESS(ROW(), COLUMN() + 12)) + 10
=INDIRECT("'[" & 'Current Cycle File Path'!A2 & "]Cycle C Day 1'!" & ADDRESS(ROW(), COLUMN() + 12)) + 30

Your original question was asking about how you can get a variable file name into 'Current Cycle File Path'!A2 so that your INDIRECT formula would work.

I'd suggest that if the only pattern you have now is the INDIRCT formulas that are entered into the Next workbook, you could replace those formulas with simply the last value 5,10,0,30 or whatever.

I could help revise the code to replace those values with formulas that don't use INDIRECT, just like the first formula we did with +25.
Would that work?

 

[johnnyL]

...
I'd suggest that if the only pattern you have now is the INDIRCT formulas that are entered into the Next workbook, you could replace those formulas with simply the last value 5,10,0,30 or whatever.

I could help revise the code to replace those values with formulas that don't use INDIRECT, just like the first formula we did with +25.
Would that work?

You mean something like the following:
1) Start Loop.
2) Save current cell value which contains just the 'amount to add' value into a variable.
3) write formula + 'amount to add' to the current cell.
4) Increment active cell.
5) Loop back.

Something like that, or is there an easier way to do it?

 

[Jerry Sullivan]

That looks perfect to me.

 

[johnnyL]

Oki Doki! Now I just gotta create these hellatious loops to write all those formulas.

Gonna have to check for: <-- this is gonna take some thinking
------------------------
cells to skip
---- Blank cells ... if so, skip it
---- Check for existing formula ... if so, skip it
---- Check for values that are constant all the time ... if so, skip it

No more cells ie. EOF (End Of File)

Basically I have 5 worksheets that contain 2 columns each that need the formulas. The 2 columns are side by side and will grab the data from the other workbook that has the 2 columns offset by 12 columns to the right.

No sex this weekend.

LOL TTYL and TY for all the pointers thus far.

Please lemme know if any more tips come to mind.