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.

 

[Peter_SSs]

Try this table instead.

I had already tried it with similar changed data. It works for me.

but whats repeated that Im trying to skip, always starts with **** in Column A.

Those rows are already skipped because they have nothing in column C.

The F5 special thing gives me Value.

The F5 thing should select all cells in column C that have values, not just C2. Here is what I get with the "F5 special thing"
What is selected is not only 'Value' in C2, but C4:C9, C12:C15 etc

The code from post 12 works as-is for me with that latest 'Defaults' sheet. If it is not working for you then can you upload a dummy file with no sensitive data and where the code is not working to DropBox or OneDrive or Google Drive and post a publicly shared link to it here so that I can take a look to see what is different about your workbook compared to mine.

 

[TheTiredEngineer]

@Peter_SSs I finally got it working. Just a few typos were keeping it from running correctly. Thank you so much for your help!!