VBA Code, how to define range of found column header properly
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: VBA Code, how to define range of found column header properly
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  2. #2
    New Member
    Join Date
    Jun 2014
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

    Quote Originally Posted by Jpngineer View Post
    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.

  3. #3
    New Member
    Join Date
    Jul 2019
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

    Quote Originally Posted by Yurik74 View Post
    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

  4. #4
    New Member
    Join Date
    Jul 2019
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    I mean it errors on line50

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,519
    Post Thanks / Like
    Mentioned
    444 Post(s)
    Tagged
    45 Thread(s)

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

    What row are your headers in on sheet2?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #6
    New Member
    Join Date
    Jul 2019
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    For sheet2 it is in row2.

    Quote Originally Posted by Fluff View Post
    What row are your headers in on sheet2?

  7. #7
    New Member
    Join Date
    Jun 2014
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

    Quote Originally Posted by Jpngineer View Post
    I mean it errors on line50

  8. #8
    New Member
    Join Date
    Jul 2019
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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
    Quote Originally Posted by Yurik74 View Post
    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

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,519
    Post Thanks / Like
    Mentioned
    444 Post(s)
    Tagged
    45 Thread(s)

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

    You need to change ColorIndex to Color
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  10. #10
    New Member
    Join Date
    Jul 2019
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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?

    Quote Originally Posted by Fluff View Post
    You need to change ColorIndex to Color
    Thank you as always.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •