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

Jpngineer

New Member
Joined
Jul 16, 2019
Messages
9
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
Code:
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.
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Yurik74

New Member
Joined
Jun 2, 2014
Messages
6
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
Code:
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

New Member
Joined
Jul 16, 2019
Messages
9
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,721
Office Version
365
Platform
Windows
What row are your headers in on sheet2?
 

Yurik74

New Member
Joined
Jun 2, 2014
Messages
6
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

New Member
Joined
Jul 16, 2019
Messages
9
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

Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,721
Office Version
365
Platform
Windows
You need to change ColorIndex to Color
 

Jpngineer

New Member
Joined
Jul 16, 2019
Messages
9
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,436
Messages
5,486,879
Members
407,569
Latest member
Huzz

This Week's Hot Topics

Top