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]

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)

Johnny, The loops don't have to be so hellatious

Here is a tip so that you will still have time for some fun this weekend!

Use this very simple structure to step through each Cell in your Range.
This is much easier than incrementing row numbers, and you won't have to check whether you have reached the last cell in the file.

Dim c as Range
For Each c in Workbooks("your WB name").Sheets(3).Range("B2:B14")
     'your code here referencing c.value, c.formula etc. 
Next c

Good luck my friend!

 

[johnnyL]

...
Use this very simple structure to step through each Cell in your Range.
This is much easier than incrementing row numbers, and you won't have to check whether you have reached the last cell in the file.

Dim c as Range
For Each c in Workbooks("your WB name").Sheets(3).Range("B2:B14")
     'your code here referencing c.value, c.formula etc. 
Next c

Good luck my friend!

Hey there again, I tried to use that bit of code, but I keep getting errors with the 'for Each' line.

I did however come up with the following code that uses a portion of what you suggested to find the first line of data that I would need to be concerned about. Basically, the top part of the worksheets contain stuff that I don't care about, so I only wanna look at the data parts in the lower portion of the worksheets. Lemme know what your opinions are about it ...

Function Start_Row_of_Data()        ' Find the start row of the data we are
'                                   ' interested in.
    Dim celll As Range
'
    CurrentRow = 1
'
    For Each celll In Range("A1:A60")
'       Check 'A' column for Left part of string = "PLANNING"
        If Left(celll.Value, 8) = "PLANNING" Then   ' If found then ...
            Start_Row_of_Data = CurrentRow + 5      '   Our data starts 5 rows down from this
            Exit Function
        Else                                        ' Otherwise ...
            CurrentRow = CurrentRow + 1             '   Increment our row counter
        End If
    Next celll                                      ' go check next cell in range for our 'flag'
'
'   Warning !!! Our 'flag' was not found ... Something is not right.
    MsgBox "Could not find the start of the data section. Program terminated!"
    Exit Function
'
End Function

 

[Jerry Sullivan]

Hi Johnny,

That's okay; however the counter that you have isn't necessary.
Instead of counting each row every time you loop, you can just find out what row your matching cell is on when/if it is found.

    For Each cell_L In Range("A1:A60")
'       Check 'A' column for Left part of string = "PLANNING"
        If Left(cell_L .Value, 8) = "PLANNING" Then   
            Start_Row_of_Data = cell_L.Row + 5   'data starts 5 rows down
            Exit Function
        End If
    Next cell_L

This minor point on style is really fussy , but I can't help from suggesting you not use celll as a variable name.
It's difficult to tell whether it is cell1 (cell one) from celll (cell L) or just a misspelliing of cell

EDIT: One other point....
FYI, Range.Find is usually more efficient for finding matching text than For....Each. Since the range you are searching is small, this won't make a difference for this code, but I wanted to point out for other stuff you might do with larger datasets.

 

[johnnyL]

...
That's okay; however the counter that you have isn't necessary.
Instead of counting each row every time you loop, you can just find out what row your matching cell is on when/if it is found.

    For Each cell_L In Range("A1:A60")
'       Check 'A' column for Left part of string = "PLANNING"
        If Left(cell_L .Value, 8) = "PLANNING" Then   
            Start_Row_of_Data = cell_L.Row + 5   'data starts 5 rows down
            Exit Function
        End If
    Next cell_L

Thank you so much for that bit of info! I am all for shortening code to do same things.

This minor point on style is really fussy , but I can't help from suggesting you not use celll as a variable name.
It's difficult to tell whether it is cell1 (cell one) from celll (cell L) or just a misspelliing of cell

That was exactly my intent, to 'misspell' cell. As far as I know, that is a reserved word that would most likely trigger an error unless I altered it's name ie. misspell it. Your suggestions of cell1 / cellL, etc, in my mind, could be considered just as confusing, if not more, as my suggestion of celll. So Touche.

EDIT: One other point....
FYI, Range.Find is usually more efficient for finding matching text than For....Each. Since the range you are searching is small, this won't make a difference for this code, but I wanted to point out for other stuff you might do with larger datasets.

I haven't crosssed that bridge yet, but I will keep that in mind. TY for all your help thus far.

 

[johnnyL]

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

Ok I have googled and I think I have come up with a couple of cmds to do what I want in the next step.

BTW the next step is to search a range and insert the formulas when needed. Cells we want to avoid putting the formulas into are, blank cells, text cells, pre existing formula cells, for now.

The cmds I found are:
cell.HasFormula - True = formula found - Skip it, next cell
IsNumber - False = text only or blank - Skip it, next cell

I think if I had those in code, that would solve almost all of what I am seeking right now.

I came up with some code, but I think it needs some improvement, so any suggestions are welcome, I know it is not quite right, but I was working off of what I could find googling this part.

    For Each cell In Range("C6:C10")
        If cell.HasFormula = True Then  ' Check to see if cell contains a formula, if yes then ...
            cell.Interior.Color = vbRed '   indicate that we will skip this cell
            GoTo GetNext:                     '   Check next cell
        End If
' Check for blank cell or a text cell
        If Application.WorksheetFunction.IsNumber(ActiveCell.Value) = False Then
                cell.Interior.Color = vbRed ' indicate that we will skip this cell
        Else                                ' Otherwise ...
            cell.Interior.Color = vbWhite   '   indicate that we will include this cell
        End If
GetNext:
    Next

Lemme know if that sparks any hints / suggestions for me.

TTYL

 

[Jerry Sullivan]

Hi Johnny,

One part of your code that would have given you problems is this line...
If Application.WorksheetFunction.IsNumber(ActiveCell.Value) = False Then

Since you stepping through each instance of variable name Cell then you want to do this instead...
If Application.WorksheetFunction.IsNumber(Cell.Value) = False Then

You can also eliminate the use of GoTo by using If...ElseIf...Else...EndIf
The code below will give you the same result and it is easier to follow.

For Each Cell In Range("C6:C10")
    With Cell
        If .HasFormula = True Then
            .Interior.Color = vbRed 'formula- skip            
        ElseIf Application.WorksheetFunction.IsNumber(.Value) = False Then
            .Interior.Color = vbYellow 'blank cell- skip
        Else
            .Interior.Color = vbWhite  'use this cell
        End If
    End With
Next

Lastly, tagging the cells by color was a good idea for learning and debugging,
but your finished code will just need to find the valid cells and it does not
need to distinguish types of invalid cells.
So we can simplify your code one more step like this...

For Each Cell In Range("C6:C10")
    With Cell
        If .HasFormula = False And _
            WorksheetFunction.IsNumber(.Value) Then
            .Interior.Color = vbGreen  'use this cell
        End If
    End With
Next

Nice going....it looks like you are learning a lot from working through this.

 

[johnnyL]

Hi Johnny,

One part of your code that would have given you problems is this line...
If Application.WorksheetFunction.IsNumber(ActiveCell.Value) = False Then

Since you stepping through each instance of variable name Cell then you want to do this instead...
If Application.WorksheetFunction.IsNumber(Cell.Value) = False Then

Ty, yeah I knew that part only checked the active cell and would present a problem.

You can also eliminate the use of GoTo by using If...ElseIf...Else...EndIf
The code below will give you the same result and it is easier to follow.

For Each Cell In Range("C6:C10")
    With Cell
        If .HasFormula = True Then
            .Interior.Color = vbRed 'formula- skip            
        ElseIf Application.WorksheetFunction.IsNumber(.Value) = False Then
            .Interior.Color = vbYellow 'blank cell- skip
        Else
            .Interior.Color = vbWhite  'use this cell
        End If
    End With
Next

Hmmm, I think I was trying 'Else If' and getting errors, again, TY!

Lastly, tagging the cells by color was a good idea for learning and debugging,
but your finished code will just need to find the valid cells and it does not
need to distinguish types of invalid cells.
So we can simplify your code one more step like this...

For Each Cell In Range("C6:C10")
    With Cell
        If .HasFormula = False And _
            WorksheetFunction.IsNumber(.Value) Then
            .Interior.Color = vbGreen  'use this cell
        End If
    End With
Next

Nice going....it looks like you are learning a lot from working through this.

I am definately learning some of this language, thanks to you and this site.

Again, Thank You !!! I think I have what I need now to complete my project.

 

[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

Dang! I thought I had what I needed to complete my objective, but I am getting errors trying to implement the changes needed.

What I am wanting to do is load up each of the first 5 sheets, one at a time, in the chosen workbook, and in each of those sheets, I want to write the formulas to range E13:F60 if the cell is determined to be a number that we want to add to the formula that will be written to the cell, so basically I want to skip cells that are blank, contain text, or contain a formula, I only want to write a formula to cells encountered in that range that contain the 'Add to amount' values that was suggested earlier on.

This is the code that I thought would work, but, sadly, it is not.

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
   
'
' Set up loop to go through first 5 sheets of the workbook ... first 5 sheets in the workbook
    For Sheet_Counter = 1 To 5           ' Sheets.Count  ... would = all sheets ;)
        Sheets(Sheet_Counter).Select     ' Select worksheet
'
        If cell.HasFormula = False And _
            WorksheetFunction.IsNumber(cell.Value) Then ' cell = number then ...
'
            cell.Interior.Color = vbGreen  'use this cell   ' indicate with green that we will include this cell
'
            With Workbooks(Next_File_Name)
'
                .Sheets(Sheet_Counter).Range("E13:F60").FormulaR1C1 = _
                    "='[" & Completed_File_Name & "]" & CurrentSheetName & "'!" & "RC[12] + " & AddAmount
     End With
        Else
            cell.Interior.Color = vbRed  'skip this cell   ' indicate with red that we are skipping this cell
        End If
    Next Sheet_Counter
End Sub

Function CurrentSheetName()
    CurrentSheetName = ActiveSheet.Name
End Function

What are the mistakes that I am making with the code that I am trying?

 

[Jerry Sullivan]

If I'm understanding what you are trying to do with the 5 sheets in both workbooks, then this should work...

Sub DirectReferenceToWorkbook_r1()
    Dim Completed_File_Name  As String
    Dim Next_File_Name As String
    Dim CompletedSheetName As String
    Dim Cell As Range
    Dim Sheet_Counter As Long
    
    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)
        ' Set up loop to go through first 5 sheets of the workbook
        For Sheet_Counter = 1 To 5
            'get corresponding sheet name from Completed_File_Name
            CompletedSheetName = Workbooks(Completed_File_Name) _
                .Sheets(Sheet_Counter).Name
            
            For Each Cell In .Sheets(Sheet_Counter).Range("E13:F60")
                If Cell.HasFormula = False And _
                        WorksheetFunction.IsNumber(Cell.Value) Then
                    Cell.Interior.Color = vbGreen  'use this cell
                    Cell.FormulaR1C1 = _
                        "='[" & Completed_File_Name & "]" & _
                          CompletedSheetName & "'!" & "RC[12] + " & Cell.Value
                Else
                    Cell.Interior.Color = vbRed  'skip this cell
                End If
            Next Cell
        Next Sheet_Counter
    End With
End Sub

The main part that you missed was stepping through each Cell of Range("E13:F60"). Other than that you got pretty close.

 

[johnnyL]

TY So much! That is almost close to working perfectly! I think the problems I have are on my end now. Lemme make some some changes to my forms and I will post back the results. Again, Thank you so much for all of the assistance thus far.