VBA Code, how to define range of found column header properly

[Jpngineer]

AC


AD

AE

AF

AG

DWG. NO

SYM.

FS991EX

MB4250

I am a newbie in VBA
The thing is I need to compare two columns of two sheets.
My biggest problem is that sheet2 is always changing (new sheet2 will be inserted every now and then) so the column position is also varying.
I think of finding first the column header (FIX name). But I am having a hard time on how to put it in code.
The above table is an example of sheet2 data. (Note:There are many columns in the sheet.)
I use below code but i do not know if this is correct. (It is not working)
I wanted to set the found column of sheet2 as range

With Worksheets("sheet2")
     With .Range("A2", .Cells(1, .Columns.Count).End(xlToLeft))
         Set DWG_COL = .Find(what:="DWG. NO", LookIn:=xlValues, lookat:=xlWhole)
         If Not DWG_COL Is Nothing Then 'if column was found set as range
            Set dwg_y = Range(DWG_COL, DWG_COL.End(xlUp)) 'set sheet2 range to be compared
         End If
     End With
     With .Range("A2", .Cells(1, .Columns.Count).End(xlToLeft))
         Set SYM_COL = .Find(what:="SYM.", LookIn:=xlValues, lookat:=xlWhole)
         If Not SYM_COL Is Nothing Then 'if column was found set as range
            Set sym_y = Range(SYM_COL, SYM_COL.End(xlUp)) 'set sheet range to be compared
         End If
     End With
 End With

Sheet1is fix so I dont have problem with that
During comparison, I use For-Each so I also dont have problem with that.
My problem is Just the sheet2 range.
Can somebody help me please.
Thank you so much.

 

[Yurik74]

I would make a couple of changes.
1. Instead of .Range("A2", .Cells(1, .Columns.Count).End(xlToLeft)) I would use .Usedrange - see line 20 and 80 in the code
2. I would add .EntireColumn to the lines 50 & 110, in case you want to assign the entire column
HTH

10 With Worksheets("sheet2")
20 With .UsedRange '''' Range("A2", .Cells(1, .Columns.Count).End(xlToLeft))
30 Set DWG_COL = .Find(what:="DWG. NO", LookIn:=xlValues, lookat:=xlWhole)
40 If Not DWG_COL Is Nothing Then 'if column was found set as range
50 Set DWG_Y = Range(DWG_COL, DWG_COL.End(xlUp)).EntireColumn 'set sheet2 range to be compared
60 End If
70 End With
80 With .UsedRange '''' Range("A2", .Cells(1, .Columns.Count).End(xlToLeft))
90 Set SYM_COL = .Find(what:="SYM.", LookIn:=xlValues, lookat:=xlWhole)
100 If Not SYM_COL Is Nothing Then 'if column was found set as range
110 Set SYM_Y = Range(SYM_COL, SYM_COL.End(xlUp)).EntireColumn 'set sheet range to be compared
120 End If
130 End With
140 End With

AC


AD

AE

AF

AG

DWG. NO

SYM.

FS991EX

MB4250

I am a newbie in VBA
The thing is I need to compare two columns of two sheets.
My biggest problem is that sheet2 is always changing (new sheet2 will be inserted every now and then) so the column position is also varying.
I think of finding first the column header (FIX name). But I am having a hard time on how to put it in code.
The above table is an example of sheet2 data. (Note:There are many columns in the sheet.)
I use below code but i do not know if this is correct. (It is not working)
I wanted to set the found column of sheet2 as range

With Worksheets("sheet2")
     With .Range("A2", .Cells(1, .Columns.Count).End(xlToLeft))
         Set DWG_COL = .Find(what:="DWG. NO", LookIn:=xlValues, lookat:=xlWhole)
         If Not DWG_COL Is Nothing Then 'if column was found set as range
            Set dwg_y = Range(DWG_COL, DWG_COL.End(xlUp)) 'set sheet2 range to be compared
         End If
     End With
     With .Range("A2", .Cells(1, .Columns.Count).End(xlToLeft))
         Set SYM_COL = .Find(what:="SYM.", LookIn:=xlValues, lookat:=xlWhole)
         If Not SYM_COL Is Nothing Then 'if column was found set as range
            Set sym_y = Range(SYM_COL, SYM_COL.End(xlUp)) 'set sheet range to be compared
         End If
     End With
 End With

Sheet1is fix so I dont have problem with that
During comparison, I use For-Each so I also dont have problem with that.
My problem is Just the sheet2 range.
Can somebody help me please.
Thank you so much.

 

[Jpngineer]

Thanks for answering,
However when I tried, it errors after dwg_y = Range(SYM_COL, SYM_COL.End(xlUp)).EntireColumn

I don't know why.
Error 1004 Application and Object Error

Thank you so much.

I would make a couple of changes.
1. Instead of .Range("A2", .Cells(1, .Columns.Count).End(xlToLeft)) I would use .Usedrange - see line 20 and 80 in the code
2. I would add .EntireColumn to the lines 50 & 110, in case you want to assign the entire column
HTH

10 With Worksheets("sheet2")
20 With .UsedRange '''' Range("A2", .Cells(1, .Columns.Count).End(xlToLeft))
30 Set DWG_COL = .Find(what:="DWG. NO", LookIn:=xlValues, lookat:=xlWhole)
40 If Not DWG_COL Is Nothing Then 'if column was found set as range
50 Set DWG_Y = Range(DWG_COL, DWG_COL.End(xlUp)).EntireColumn 'set sheet2 range to be compared
60 End If
70 End With
80 With .UsedRange '''' Range("A2", .Cells(1, .Columns.Count).End(xlToLeft))
90 Set SYM_COL = .Find(what:="SYM.", LookIn:=xlValues, lookat:=xlWhole)
100 If Not SYM_COL Is Nothing Then 'if column was found set as range
110 Set SYM_Y = Range(SYM_COL, SYM_COL.End(xlUp)).EntireColumn 'set sheet range to be compared
120 End If
130 End With
140 End With

 

[Jpngineer]

I mean it errors on line50

 

[Fluff]

What row are your headers in on sheet2?

 

[Jpngineer]

For sheet2 it is in row2.

What row are your headers in on sheet2?

 

[Yurik74]

Most likely that is because the code could find the value you are looking for and the range is nothing, so it can't assign the range.

I mean it errors on line50

 

[Jpngineer]

What does it mean?
Sorry, I am really a newbie.

I also used below code as an alternative, actually this is what I am trying to do as a whole but the error occurs at For-Each If c1=c2 line

Sub LookForMatches()
     Dim dwg_x As Range, dwg_y As Range, c1 As Range, c2 As Range
     Dim sym_x As Range, sym_y As Range, c3 As Range, c4 As Range

 'set ranges
     'Set i = Sheets("datay").Range("TEST")
     Set dwg_x = Sheets("datax").Range("C5", Sheets("datax").Range("C" & Rows.Count).End(xlUp))
     Set sym_x = Sheets("datax").Range("F5", Sheets("datax").Range("F" & Rows.Count).End(xlUp))

     Set DWG_COL = Sheets("datay").Range("A2:P2").Find("DWG. NO", , xlValues, xlWhole, , , True)
     Set SYM_COL = Sheets("datay").Range("A2:P2").Find("SYM", , xlValues, xlWhole, , , True)

     Set dwg_y = DWG_COL.EntireColumn
     Set sym_y = SYM_COL.EntireColumn
'reset colour
     dwg_x.Interior.Color = 16777215
     dwg_y.Interior.Color = 16777215
     sym_x.Interior.Color = 16777215
     sym_y.Interior.Color = 16777215
 'loop values in range
     For Each c1 In dwg_x
         If Not c1.Interior.ColorIndex = 16777215 And c1 <> "" And c1 <> 0 Then
             For Each c2 In dwg_y
                 If c1 = c2 And c2.Address <> c1.Address Then
                     c1.Interior.Color = RGB(255, 255, 0)
                     c2.Interior.Color = RGB(255, 255, 0)
                 End If
             Next c2
         End If
     Next c1
 'loop values in next range
     For Each c3 In sym_x
         If Not c3.Interior.ColorIndex = 16777215 And c3 <> "" And c3 <> 0 Then
             For Each c4 In sym_y
                 If c3 = c4 And c4.Address <> c3.Address Then
                     c3.Interior.Color = RGB(255, 255, 0)
                     c4.Interior.Color = RGB(255, 255, 0)
                 End If
             Next c4
         End If
     Next c3
 End Sub

Most likely that is because the code could find the value you are looking for and the range is nothing, so it can't assign the range.

Thanks for your kindness

 

[Fluff]

You need to change ColorIndex to Color

 

[Jpngineer]

I changed the ColorInde to Color.
No error occurred but the matched data was not highlighted.

What do you think was lacking with the code?

You need to change ColorIndex to Color

Thank you as always.