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.
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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.
 
Upvote 0
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
 
Upvote 0
What row are your headers in on sheet2?
 
Upvote 0
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 :)
 
Upvote 0
You need to change ColorIndex to Color
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top