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

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

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,857
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,857
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,706
Messages
5,488,418
Members
407,638
Latest member
brandynl

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top