Find Values of Cells and Replace values of Named Cells between Sheets

[TheTiredEngineer]

Hi All,

Im trying to create a macro that sets defaults on the "Main" sheet (or any sheet) using values from the "Defaults" sheet. Ive got screenshots below of a sample workbook as I cant share any screenshots of the actual workbook.

What the macro needs to do is search in the defaults sheet, increment by row to grab a variable name from each cell in column B, the default value in Column C and the sheet name given in Column F. In the actual workbook, I have 30 sheets to choose from, with about 700 named variables, which are broken up into sections based on the sheet name. Then using those values it found, it needs to find the sheet and named cell and set the value of the named cell to the value it grabbed from the defaults sheet. I hope I explained it ok. I know I need to use Find/Replace to do this but Im not really sure where or how to create this.

Actual data in the defaults sheet starts on row 3 and every so often has to skip a couple of rows of non-data to move on.

Option Explicit

Dim i As Integer
Dim n As Integer


Sub ResetToDefaults()
Dim c As Range
Dim firstAddress As String

With ThisWorkbook
For i = 3 To Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row

    Dim lookupvalue As String

    lookupvalue = Sheets(1).Cells(i, 1).Value
    Debug.Print lookupvalue & vbCrLf

    For n = 1 To Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row

        If TypeName(Application.Search(lookupvalue, Sheets(2).Cells(n, 1))) = "VarA" Then
                Sheets(2).Cells(n, 1).Value = Sheets(1).Cells(i, 2).Value
                GoTo exitloop
        End If
    Next n
exitloop:
Next i

End With

End Sub

Heres some code I started with. It seems to be grabbing the variable name correctly but I dont know where to go from here. It doesnt get into the For loop for whatever reason. Im very unfamiliar with this advanced coding, so any help is very appreciated.

 

[TheTiredEngineer]

After much trial and error today, I got it to work for multiple sheets and with blank lines in the defaults sheet. Now I am trying to get it to skip rows that have anything other than numbers in column 3. Im getting stuck there - it runs without errors but its not actually doing anything when theres text in column [3] of the defaults sheet like so:

Option Explicit

Dim i As Integer, n As Integer

Dim lookupnamedCell As String, lookupsheetname As String
Dim lookupvalue As Double
Dim lookupsheetnum As Integer, defaultsheetnum As Integer
Dim defaultvalue As Double


Sub ResetToDefaults()


With ThisWorkbook
    defaultsheetnum = Sheets("Defaults").Index
    Application.ScreenUpdating = False
    
For i = 3 To Sheets(defaultsheetnum).Cells(Rows.Count, 1).End(xlUp).Row


    'If Sheets(defaultsheetnum).Cells(i, 1).Value <> "" Then
    
    If IsNumeric(Sheets(defaultsheetnum).Cells(i, 1).Value) = False And WorksheetFunction.IsText(Sheets(defaultsheetnum).Cells(i, 1).Value) = False Then
    
        lookupnamedCell = Sheets(defaultsheetnum).Cells(i, 2).Value
        lookupvalue = Sheets(defaultsheetnum).Cells(i, 3).Value
        lookupsheetname = Sheets(defaultsheetnum).Cells(i, 6).Value
        lookupsheetnum = Sheets(lookupsheetname).Index
    
        Debug.Print lookupnamedCell '& vbCrLf
        Debug.Print lookupsheetname

        For n = 0 To Sheets(lookupsheetnum).Cells(Rows.Count, 1).End(xlUp).Row
    
            'code needs to find the named cell/range from lookupnamedCell, and find what value it refers to on sheet 2
            'And then replace that value with the lookupvalue
            
            .Sheets(lookupsheetnum).Evaluate(lookupnamedCell) = lookupvalue

        Next n
    End If

exitloop:
Next i

Application.ScreenUpdating = True
End With

End Sub

 

[MagicSteve]

I had a look at this and here is a simplified version
I hope these suggestions will make things easier for you. You are really doing everything the hard way.
Open the vb editor and open the windows to view local, watch and immediate windows
Put your cursor on "Sub ResetToDefaults" and press F8 to start the debugger.
Continue to press F8 and go through your code line by line. Highlight a variable and drag it to the watch window to see the value
------------------------------------------------------------------------------------------------------------------
Option Explicit

Sub ResetToDefaults()

' variables declared locally in the sub
Dim i As Integer
Dim defaultsheetnum As Integer

' define a sheet so that you don't need to use awkward Sheets(numberHere)
Dim mySheet As Worksheet

' just use an integer index like i or j since you're using i
' example Dim j as integer
' j = Sheets("Defaults").Index
' even better, assign a "code name" to the sheet in the project explorer and then use
' myValue = shDefaults.Cells(row,col).value

defaultsheetnum = Sheets("Defaults").Index

Set mySheet = Sheets(defaultsheetnum)

i = 3

Dim temp As String
Dim temp2 As String

temp = Sheets(defaultsheetnum).Cells(i, 1).Value
temp2 = Sheets(defaultsheetnum).Cells(i, 1).Value
Debug.Print IsNumeric(Sheets(defaultsheetnum).Cells(i, 1).Value)
Debug.Print WorksheetFunction.IsText(Sheets(defaultsheetnum).Cells(i, 1).Value)

' same thing using "mySheet" notice that Sheets(defaultsheetnum) is just too long and unnecessary
temp = mySheet.Cells(i, 1).Value
' temp2 = the same thing, you're referencing the same cell again ?? you already have this value

' don't test for something = true or something = false, test for (thing that evaluates to true/false) or NOT (thing that evaluates to true/false)
If Not IsNumeric(temp) Then
MsgBox "Is numeric is false"
End If

End Sub

 

[TheTiredEngineer]

I had a look at this and here is a simplified version
I hope these suggestions will make things easier for you. You are really doing everything the hard way.
Open the vb editor and open the windows to view local, watch and immediate windows
Put your cursor on "Sub ResetToDefaults" and press F8 to start the debugger.
Continue to press F8 and go through your code line by line. Highlight a variable and drag it to the watch window to see the value
------------------------------------------------------------------------------------------------------------------
Option Explicit

Sub ResetToDefaults()

' variables declared locally in the sub
Dim i As Integer
Dim defaultsheetnum As Integer

' define a sheet so that you don't need to use awkward Sheets(numberHere)
Dim mySheet As Worksheet

' just use an integer index like i or j since you're using i
' example Dim j as integer
' j = Sheets("Defaults").Index
' even better, assign a "code name" to the sheet in the project explorer and then use
' myValue = shDefaults.Cells(row,col).value

defaultsheetnum = Sheets("Defaults").Index

Set mySheet = Sheets(defaultsheetnum)

i = 3

Dim temp As String
Dim temp2 As String

temp = Sheets(defaultsheetnum).Cells(i, 1).Value
temp2 = Sheets(defaultsheetnum).Cells(i, 1).Value
Debug.Print IsNumeric(Sheets(defaultsheetnum).Cells(i, 1).Value)
Debug.Print WorksheetFunction.IsText(Sheets(defaultsheetnum).Cells(i, 1).Value)

' same thing using "mySheet" notice that Sheets(defaultsheetnum) is just too long and unnecessary
temp = mySheet.Cells(i, 1).Value
' temp2 = the same thing, you're referencing the same cell again ?? you already have this value

' don't test for something = true or something = false, test for (thing that evaluates to true/false) or NOT (thing that evaluates to true/false)
If Not IsNumeric(temp) Then
MsgBox "Is numeric is false"
End If

End Sub

Hi MagicSteve,

I tried to cleanup my code some. I didnt understand a lot of what you were saying about stuff like temp2 being repeated or me making it too complicated. I'll try to explain the purpose for so many cell lookups: the code is pulling values from Columns B, C and F, so theres has to be a separate cell lookup for each of those values. The 2nd for loop uses those 3 values to find and replace the values in the sheets with the ones from the defaults sheet.

Im still struggling with getting the loop to skip text values in the numerical column. Whenever I try, it throws some error and, in the debugger, it shows ncell as blank. I couldnt figure out a way to cleanup this: If Not (IsNumeric(myDefaults.Cells(i, 2).Value)) Or Not (Wf.IsNonText(myDefaults.Cells(i, 3))) Then. to skip text and blanks in column 3. I tried pulling those values within the If statement out but then it breaks everything.

Im thinking the If statement needs something like GoTo and skip to function but I havent tried anything yet. What are your thoughts?

Dim i As Integer, n As Integer
Dim ncell As String, sname As String
Dim lookupvalue As Double, snum As Integer, dnum As Double
Dim Wf As WorksheetFunction
Dim myDefaults As Worksheet, mySheets As Worksheet
Dim Ws As Worksheet, Wb As Workbook

Sub ResetToDefaults()

Set Wf = WorksheetFunction
Set Wb = ThisWorkbook

With Wb
    
    Application.ScreenUpdating = False
    dnum = Sheets("Defaults").Index
    
    Set myDefaults = Sheets(dnum)
    
For i = 3 To myDefaults.Cells(Rows.Count, 1).End(xlUp).Row
 
    If Not (IsNumeric(myDefaults.Cells(i, 2).Value)) Or Not (Wf.IsNonText(myDefaults.Cells(i, 3))) Then
        
        ncell = myDefaults.Cells(i, 2).Value 'Column B, Variable Name
        lookupvalue = myDefaults.Cells(i, 3).Value 'Column C, Variable Value (lookup)
        sname = myDefaults.Cells(i, 6).Value 'Column F, Sheet Name
        snum = Sheets(sname).Index 'sheet number
    
        Debug.Print ncell
        Debug.Print sname

        For n = 0 To Sheets(snum).Cells(Rows.Count, 1).End(xlUp).Row
            
            .Sheets(snum).Evaluate(ncell) = lookupvalue

        Next n
    End If

exitloop:
Next i
Application.ScreenUpdating = True

End With
'MsgBox "Reset to Defaults"
End Sub

 

[MagicSteve]

If you're using the debugger that's good. You can hover over a variable and it will show you the value. you can use Debug.Print nameHere in the immediate window
and also drag a variable into the watch window
You can view 3 windows in the debugger along with your code, the "locals", "immediate" and "watches" You want to learn to use all 3

I copied the image of your spreadsheet and got numeric values 2.5, .1, 10, 200 ... in column C
when you use IsNumeric(sheet.Cells(i,2) you are getting the value in column B.That doesn't look right. Col B is never numeric. It is "varA" "varB' "varC" ...

IsNonText(sheetl.Cells(i,3) is indeed looking at column C where the value in row 10 is "Value"
But you really want to use IsNumeric. Non text could be date, time, currency ... lot's of ways to be non-text

Here's code to go down the sheet looking for numeric in column C
----------------------------------------------------------------------------------------

Sub ShowNumericVrsNonNumeric()

  Dim i As Integer
  Dim temp As String
  Dim mySheet As Worksheet

  i = 1 ' starting at 1, even though you know you could start at row 3 
  Set mySheet = ThisWorkbook.Worksheets("Defaults")
  ' set a debugger breakpoint on the line above and examine mySheet after the assignment
  ' use the "Watches" window to see the sheet object. click on "mySheet" and drag it into the Watches window
  temp = mySheet.Cells(i, 3).Value
  ' you can stop here and examine temp (debug.print temp, look in the "locals" window, or hover over temp in
  ' the code, or click and drag it into the watch window
  Do While temp <> ""
    If IsNumeric(temp) Then
      Debug.Print "Found numeric value: " & temp & " in row " & i
    Else
      Debug.Print "Value " & temp & " in row " & i & " is not numeric"
    End If
    i = i + 1
    temp = mySheet.Cells(i, 3).Value
  Loop
end sub

-----------------------------------------------------------
Run that routine and you'll see this in the immediate window
Notice that I'm starting at row 1, so you get the messages for "DEFAULT VALUES" row 1
and "Value" (row 2)

Value DEFAULT VALUES in row 1 is not numeric
Value Value in row 2 is not numeric
Found numeric value: 2.5 in row 3
Found numeric value: 0.1 in row 4
Found numeric value: 10 in row 5
Found numeric value: 200 in row 6
Found numeric value: 200 in row 7
Found numeric value: 30 in row 8
Found numeric value: 1500 in row 9
Value Value in row 10 is not numeric
Found numeric value: 25000 in row 11

I hope this helps.
Magic Steve

 

[Peter_SSs]

@MagicSteve
Welcome to the MrExcel board! When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags in your most recent post above for you this time.

 

[TheTiredEngineer]

@MagicSteve

Thanks for your help. I just saw your response. I had spent all day working on this to get it to work. I kept struggling with my original method and realized that the .End(xlUp) check doesnt allow skipping blanks or specific cells, so I didnt see what you had wrote. I went back to the drawing board and put together another piece of code that functions a bit differently. It works correctly even when I continue to add sections and new variables.

Heres the sheet for the defaults; I just kept adding more rows and different sheet names to test the code out.

Heres the outputs Im getting which is exactly what I was looking for. The code not only skips blanks and unnecessary text by hiding the rows, it also now replaces the looked-up cells in the other sheets with the looked-up values.

VarA
 2.5
 2
Main
VarB
 0.1
 2
Main
VarC
 10
 2
Main
VarD
 200
 2
Main
VarE
 200
 2
Main
VarF
 30
 2
Main
VarG
 1500
 3
Design
VarH
 25000
 4
Construct
VarJ
2-stage
 5
PartX
VarK
3-stage
 6
PartY

Heres my final code:

Option Explicit

Dim i As Integer, n As Integer, snum As Integer
Dim ncell As String, sname As String, dcell As String, nvalue As String, Fsheets As String, Isheets As String, firstAddress As String
Dim dvalue As Double, dnum As Double
Dim Wf As WorksheetFunction
Dim myDefaults As Worksheet, mySheets As Worksheet, mySnum As Worksheet, Ws As Worksheet, Wb As Workbook

Dim firstVal As Range, x As Range, firstVar As Range
Dim LastRow As Long, LastRow2 As Long, k As Long

Sub findReplaceVal()
 Application.ScreenUpdating = False
Set Wf = WorksheetFunction
Set Wb = ThisWorkbook

dnum = Sheets("Defaults").Index
Set myDefaults = Sheets(dnum)

With Worksheets("Defaults").Range("a3:ZZ3") 'Selected Range on defaults sheet

LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    For i = 3 To LastRow 'loop per row on Defaults

    dcell = myDefaults.Cells(i, 1).value 'Column A
    nvalue = myDefaults.Cells(i, 3).value 'Column C, Variable Value (lookup)
    ncell = myDefaults.Cells(i, 2).value 'Column B, Variable Name
   
        If Not (IsNumeric(myDefaults.Cells(i, 2).value)) Or Not (Wf.IsNonText(myDefaults.Cells(i, 3))) Then
                'Makes sure that the cell values arent blank and that the first cell is not = Title
                If dcell <> "Title" And Len(ncell) > 0 And Len(dcell) > 0 Then
                    If myDefaults.Cells(i, 1).value = "Title" Then
                        myDefaults.Rows(i).EntireRow.Hidden = True
                        Debug.Print myDefaults.Cells(i, 1).value
                    Else
                        'Find Variable Name
                        Set firstVar = myDefaults.Columns(2).Find("Var*", myDefaults.Cells(i - 1, 2), xlValues, _
                                                xlWhole, xlByColumns, xlNext)
                        Debug.Print firstVar
                        'Find Variable Value
                        Set firstVal = myDefaults.Columns(3).Find("*", myDefaults.Cells(i - 1, 3), xlValues, _
                                                            xlWhole, xlByColumns, xlNext)
                        Debug.Print firstVal
                        'Find Sheet Name
                        Fsheets = myDefaults.Columns(6).Find("*", myDefaults.Cells(i - 1, 6), xlValues, _
                                                            xlWhole, xlByColumns, xlNext)
                        'Find the sheet number based on Fsheets
                         snum = Sheets(Fsheets).Index
                       
                        Debug.Print snum
                        Debug.Print Fsheets
                       
                        LastRow2 = Worksheets(snum).Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                       
                        For n = 0 To LastRow2 'loop per row on snum sheet
                            Worksheets(snum).Range(firstVar) = firstVal
                        Next n
                    End If
                End If
        End If
exitloop:
    Next i

End With
Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

@TheTiredEngineer
Glad you have something that is working. However, from what I can see the following much simpler code seems to do the same job. Perhaps you could give it a try with a copy of your workbook.

I have assumed that there will be at least one number in column C of 'Defaults' and that for each of those numbers in that column, the relevant worksheet listed in col F exists as does the relevant named range in col B

Sub Set_Defaults()
  Dim c As Range
  
  For Each c In Sheets("Defaults").Columns("C").SpecialCells(xlConstants, xlNumbers)
    Sheets(c.Offset(, 3).Value).Range(c.Offset(, -1).Value).Value = c.Value
  Next c
End Sub

 

[TheTiredEngineer]

@TheTiredEngineer
Glad you have something that is working. However, from what I can see the following much simpler code seems to do the same job. Perhaps you could give it a try with a copy of your workbook.

I have assumed that there will be at least one number in column C of 'Defaults' and that for each of those numbers in that column, the relevant worksheet listed in col F exists as does the relevant named range in col B

Sub Set_Defaults()
  Dim c As Range
 
  For Each c In Sheets("Defaults").Columns("C").SpecialCells(xlConstants, xlNumbers)
    Sheets(c.Offset(, 3).Value).Range(c.Offset(, -1).Value).Value = c.Value
  Next c
End Sub

Woah you are a magic guru!! This is so close to working perfectly.

I went and ran it and it works for numbers only. So if whatever reason the defaults use text values (like in VarJ and VarK), it completely skips over those values and sheets. Is there a way to get it to find text values in Column C but hide those rows that have specific text like I had in my code?

 

[Peter_SSs]

I went and ran it and it works for numbers only.

Hmm, I thought that is what you wanted.

I am trying to get it to skip rows that have anything other than numbers in column 3

This just skips blanks (if they are truly blank) and cells with the heading "Value". Is that what you want?

Sub Set_Defaults_v2()
  Dim c As Range
  
  For Each c In Sheets("Defaults").Columns("C").SpecialCells(xlConstants)
    If LCase(c.Value) <> "value" Then Sheets(c.Offset(, 3).Value).Range(c.Offset(, -1).Value).Value = c.Value
  Next c
End Sub