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]

Hmm, I thought that is what you wanted.


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

Hi Peter,

Thanks for trying to help!

What I wanted is that the code skips blanks and that line with the headers like in the screenshot. That header is repeated several times throughout the sheet so it'll have to be skipped over and over to capture only the useable values.

I think I need something along the lines of If Lcase(c.Offset(,-3).Value)="Title" Then c.EntireRow.Hidden = True

 

[Peter_SSs]

I think I need something along the lines of If Lcase(c.Offset(,-3).Value)="Title" Then c.EntireRow.Hidden = True

1. There should be no need to actually hide any rows to achieve your goal. As well as not being necessary, hiding would slow the code needlessly.
   
   
2. What you say you think to need is to look for the heading "Title" in column A to decide which rows to hide or skip. My code was looking for the heading "Value" in column C since in post 1 and post 2 your images showed that heading in column C everywhere there was "Title" in column A.
   However, I do notice that in your later image (post 7) that column C heading has changed from "Value" to "DefVal" and I had missed that change when I wrote my code.

So, let's use column A as you suggested. My code would become ..

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

 

[TheTiredEngineer]

@Peter_SSs

This is perfect! It works on the test workbook. Now I get to implement it on the full workbook. Could you please explain the .SpecialCells(xlConstants) section? I dont understand that bit.

 

[Peter_SSs]

Could you please explain the .SpecialCells(xlConstants) section? I dont understand that bit.

That just looks at all the cells in column C that have values in them. It is an easy way to skip the blanks rather than checking line-by-line.

 

[TheTiredEngineer]

@Peter_SSs

I tried to run the code in my actual workbook today. Im running into an error. Its starting to check for c on the first line, but because of the header, theres a blank on row 1, so the value shows as Nothing. I tried adding a check for Not c.value Is Nothing in the If statement, but no luck. It says on the c.offset line <object variable or with block variable not set>. Not sure what that means. Row 2 has "Title" which we were already trying to skip that. Row 3 has kind of a header to break the sections up.

If LCase(c.Offset(, -2).Value) <> "Title" And Not LCase(c.Offset(, -2).Value) Is Nothing Then Sheets(c.Offset(, 3).Value).Range(c.Offset(, -1).Value).Value = c.Value

 

[Peter_SSs]

If LCase(c.Offset(, -2).Value) &lt;&gt; "Title" And Not LCase(c.Offset(, -2).Value) Is Nothing Then Sheets(c.Offset(, 3).Value).Range(c.Offset(, -1).Value).Value = c.Value

Your test code line will not work because "Title" is not in lower case "title" in that code. My code was making an allowance in case "Title" was ever written as "TITLE" or "title" etc.

If you still need help, could you give us a sample of say 10-15 rows of the Defaults sheet with XL2BB so we can see/test with the sort of data and layout that you actually have?
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

 

[TheTiredEngineer]

Your test code line will not work because "Title" is not in lower case "title" in that code. My code was making an allowance in case "Title" was ever written as "TITLE" or "title" etc.

If you still need help, could you give us a sample of say 10-15 rows of the Defaults sheet with XL2BB so we can see/test with the sort of data and layout that you actually have?
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

Heres the sample Defaults sheet. I cant share the actual defaults sheet from my workbook. Its proprietary information, even just a snippet.

Ive reproduced the sample Defaults sheet exactly how it is in the actual so we can get a code that works exactly how I need it to. Theres lots of examples of values that the defaults sheet uses: text, numbers, combination of both and even whole sentences.

ResetToDefaultsExample.xlsm
	A	B	C	D	E	F
1	DEFAULT VALUES
2	Title	Variable	Value	Unit	Cell #	Sheet
3	**** DEFAULT DESIGN SUMMARY INFORMATION ****
4	Variable A	VarA	2.5			Main
5	Variable B	VarB	0.1	MGD		Main
6	Variable C	VarC	10	mg/L		Main
7	Variable D	VarD	200	mg/L		Main
8	Variable E	VarE	200	mg/L		Main
9	Variable F	VarF	30	mg/L		Main
10
11	**** DEFAULT DESIGN PARAMETERS ****
12	Variable G	VarG	1500	mg/L		Design
13	Use Variable G	UseG	Y			Design
14	Design G+1	Des1	N			Design
15	Design G+2	Des2	Y			Design
16
17	**** DEFAULT CONSTRUCT PARAMETERS ****
18	Variable H	VarH	25000	mg/L		Construct
19	Variable T	VarT	5	ft		Construct
20	Variable BB	VarBB	Model XYZ-ABC			Construct
21
22	**** DEFAULT PARTX PARAMETERS ****
23	Variable J	VarJ	Y			PartX
24	Variable LL	VarLL	A random warranty statement that could be several lines long			PartX
25	Variable ZZ	VarZZ	N			PartX
26	Variable MM	VarMM	N			PartX
27
28	**** DEFAULT PARTY PARAMETERS ****
29	Variable K	VarK	5-stage			PartY
30	Variable PA	VarPA	ANSI A-36 Carbon Steel			PartY
31	Variable QQ	VarQQ	Titanium			PartY
Default_Vals

Cell Formulas
Range	Formula
B4:B9,B29,B23,B18:B19,B12:B15	B4	=LEFT(A4,3)&RIGHT(A4,1)
B20,B30:B31,B24:B26	B20	=LEFT(A20,3)&RIGHT(A20,2)

Cells with Data Validation
Cell	Allow	Criteria
C29	List	2-stage,3-stage,4-stage,5-stage

 

[TheTiredEngineer]

@Peter_SSs fyi in case you didnt see my post. Also wanted to mention, I counted the number of named cells today and its 1096 total so Im hoping we can get the code to work so I can test it out in the next couple of days.

 

[Peter_SSs]

Thanks for the sample worksheet.

Its starting to check for c on the first line, but because of the header, theres a blank on row 1, so the value shows as Nothing.

There seems to be nothing in that worksheet related to the above comment. There is nothing in the first cell in column C so that row is not processed by my code. To test, try this.

1. Select the whole of column C by clicking the 'C' label at the top
2. Press F5 and choose 'Special ...'
3. Select 'Constants' & click 'OK"

For me with that sample sheet the first cell selected is C3 and since col A in that row is 'Title' the row is skipped. All the other selected rows appear to be the ones you want processed, and they are the ones that my code will process.

I cannot fully test with that sample worksheet as-is though because you have in column B 'Des1' and 'Des2'. These are not valid named range names. otherwise the code from post 12 is working for me.

I counted the number of named cells today and its 1096

In that case, once we get some code that is working, we might change it to work faster as that number of replacements with the existing code structure may be a bit slow.

But let's try to get it working first.

 

[TheTiredEngineer]

Thanks for the sample worksheet.


There seems to be nothing in that worksheet related to the above comment. There is nothing in the first cell in column C so that row is not processed by my code. To test, try this.

1. Select the whole of column C by clicking the 'C' label at the top
2. Press F5 and choose 'Special ...'
3. Select 'Constants' & click 'OK"

For me with that sample sheet the first cell selected is C3 and since col A in that row is 'Title' the row is skipped. All the other selected rows appear to be the ones you want processed, and they are the ones that my code will process.

I cannot fully test with that sample worksheet as-is though because you have in column B 'Des1' and 'Des2'. These are not valid named range names. otherwise the code from post 12 is working for me.



In that case, once we get some code that is working, we might change it to work faster as that number of replacements with the existing code structure may be a bit slow.

But let's try to get it working first.

Sorry about that, was in a hurry and didnt actually name the cells to catch that mistake. Try this table instead. Most of this is the same as the original defaults sheet. The only thing Ive changed is the repeated row. At the top shows Title, Variable, Value, etc, but whats repeated that Im trying to skip, always starts with **** in Column A. The F5 special thing gives me Value.

ResetToDefaultsExample.xlsm
	A	B	C	D	E	F
1	DEFAULT VALUES
2	Title	Variable	Value	Unit	Cell #	Sheet
3	**** DEFAULT DESIGN SUMMARY INFORMATION ****
4	Variable A	VarA	2.5			Main
5	Variable B	VarB	0.1	MGD		Main
6	Variable C	VarC	10	mg/L		Main
7	Variable D	VarD	200	mg/L		Main
8	Variable E	VarE	200	mg/L		Main
9	Variable F	VarF	30	mg/L		Main
10
11	**** DEFAULT Design PARAMETERS ****
12	Variable G	VarG	1500	mg/L		Design
13	Use Variable G	UseG	Y			Design
14	Use Variable G for Design	UseGDes	N			Design
15	Use Variable G for Design +2	UseGDes2	Y			Design
16
17	**** DEFAULT CONSTRUCT PARAMETERS ****
18	Variable H	VarH	25000	mg/L		Construct
19	Variable T	VarT	5	ft		Construct
20	Variable BB	VarBB	Model XYZ-ABC			Construct
21
22	**** DEFAULT PARTX PARAMETERS ****
23	Variable J	VarJ	Y			PartX
24	Variable LL	VarLL	A random warranty statement that could be several lines long			PartX
25	Variable ZZ	VarZZ	N			PartX
26	Variable MM	VarMM	N			PartX
27
28	**** DEFAULT PARTY PARAMETERS ****
29	Variable K	VarK	5-stage			PartY
30	Variable PA	VarPA	ANSI A-36 Carbon Steel			PartY
31	Variable QQ	VarQQ	Titanium			PartY
Default_Vals

Cell Formulas
Range	Formula
B4:B9,B29,B23,B18:B19,B12:B13	B4	=LEFT(A4,3)&RIGHT(A4,1)
A11	A11	="**** DEFAULT "& F12 &" PARAMETERS ****"
B20,B30:B31,B24:B26	B20	=LEFT(A20,3)&RIGHT(A20,2)

Cells with Data Validation
Cell	Allow	Criteria
C29	List	2-stage,3-stage,4-stage,5-stage