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.
 
You filling the entire columns with a white background & then looping through the columns & checking if the background is NOT white.
As it will always be white, the inner loops never trigger.
Try stepping through the code with F8 & you will see that happening.
 
Upvote 0

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

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