Declaration Issue

Herbalgiraffe

New Member
Joined
Feb 22, 2020
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Hey Y'all,

So I am making a loop to check a list in one workbook against a list in another workbook, but when I try to run the code, it only accepts the declaration for the workbook that is active and gives a runtime 9 "object out of range" when it gets to the second workbook, and if I am on the other workbook, it will give the same error for the first workbook that I am no longer looking at. If I stick a line in to activate the other sheet, it errors out with "Can't execute code in break mode". How do I successfully declare both ranges on the two different workbooks so that I can get into the loop?

VBA Code:
Sub Highlight()

    Dim Ws1 As Range, Ws2 As Range, Wb1 As Workbook, Wb2 As Workbook, C As Range, fn As Range
       
    Workbooks.Open "T:\Customer Service\Site\Pre-Order Skus.xlsx"
      
    Set Wb1 = Workbooks("Test Order File.xlsm")
    Set Ws1 = Wb1.Worksheets("Test").Range("A1", Worksheets("Test").Cells(Rows.Count, 1).End(xlUp))
    Set Wb2 = Workbooks("Pre-Order Skus.xlsx")
   'Here is where I tried activating the other workbook to get it to see Ws2.
    Set Ws2 = Wb2.Worksheets("Sheet1").Range("A1", Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp))
       
    Worksheets("Sheet1").Range("A1").Select
   
    For Each C In Ws1
        Set fn = Ws2.Find(C.Value, , xlValues, xlWhole)
            If Not fn Is Nothing Then
                C.Interior.Color = vbYellow
            End If
    Next
   
End Sub
 
Last edited by a moderator:

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
801
Office Version
  1. 2013
Platform
  1. Windows
VBA Code:
Dim Ws1 As Range, Ws2 As Range,
Should be
Code:
Dim Ws1 As Worksheets
Dim Ws2 As Worksheets
 

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
801
Office Version
  1. 2013
Platform
  1. Windows
Nop
Code:
Dim Ws1 ,ws2 As long
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,534
Office Version
  1. 365
Platform
  1. Windows
It is not related to your declarations but you have not qualified your range references fully. Try these changes

Rich (BB code):
    Set Ws1 = Wb1.Worksheets("Test").Range("A1", Wb1.Worksheets("Test").Cells(Rows.Count, 1).End(xlUp))
    Set Wb2 = Workbooks("Pre-Order Skus.xlsx")
   'Here is where I tried activating the other workbook to get it to see Ws2.
    Set Ws2 = Wb2.Worksheets("Sheet1").Range("A1", Wb2.Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp))
 
Solution

Herbalgiraffe

New Member
Joined
Feb 22, 2020
Messages
11
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

It is not related to your declarations but you have not qualified your range references fully. Try these changes

Rich (BB code):
    Set Ws1 = Wb1.Worksheets("Test").Range("A1", Wb1.Worksheets("Test").Cells(Rows.Count, 1).End(xlUp))
    Set Wb2 = Workbooks("Pre-Order Skus.xlsx")
   'Here is where I tried activating the other workbook to get it to see Ws2.
    Set Ws2 = Wb2.Worksheets("Sheet1").Range("A1", Wb2.Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp))
That did the trick! All of those lines work wonderfully now, but I came across an error elsewhere that I am not sure how to overcome in another section of the same code. The list that needs to be compared from Ws1 to Ws2 is actually located in column C instead of column A, so using variable C for each cell in the loop seems to be looking through column A by default. Is there a way to set it up to cycle through column C instead? I tried to use offset, but that came back with "compile error, variable required- can't assign to this expression". How can I set variable C to be in column C?

VBA Code:
Sub Highlight()

Dim Ws1 As Range, Ws2 As Range, Wb1 As Workbook, Wb2 As Workbook, C As Range, fn As Range

Workbooks.Open "T:\Customer Service\Site\Pre-Order Skus.xlsx"

Set Wb1 = Workbooks("Test Order File.xlsm")
Set Ws1 = Wb1.Worksheets("Test").Range("A1", Wb1.Worksheets("Test").Cells(Rows.Count, 1).End(xlUp))
Set Wb2 = Workbooks("Pre-Order Skus.xlsx")
Set Ws2 = Wb2.Worksheets("Sheet1").Range("A1", Wb2.Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp))

Wb1.Worksheets("Test").Range("A1").Select
'Excel didn't like that
For Each C.Offset(0, 2) In Ws1
Set fn = Ws2.Find(C.Value, , xlValues, xlWhole)
If Not fn Is Nothing Then
C.Interior.Color = vbYellow
End If
Next

End Sub
VBA Code:
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,150
Office Version
  1. 365
Platform
  1. Windows
Are the values in col C of both workbooks?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,150
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
VBA Code:
Sub Highlight()

    Dim Ws1 As Range, Ws2 As Range, Wb1 As Workbook, Wb2 As Workbook, C As Range, fn As Range
       
    Workbooks.Open "T:\Customer Service\Site\Pre-Order Skus.xlsx"
      
    Set Wb1 = Workbooks("Test Order File.xlsm")
    Set Ws1 = Wb1.Worksheets("Test").Range("C1", Wb1.Worksheets("Test").Cells(Rows.Count, 3).End(xlUp))
    Set Wb2 = Workbooks("Pre-Order Skus.xlsx")
   'Here is where I tried activating the other workbook to get it to see Ws2.
    Set Ws2 = Wb2.Worksheets("Sheet1").Range("A1", Wb2.Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp))
       
'    Worksheets("Sheet1").Range("A1").Select
   
    For Each C In Ws1
        Set fn = Ws2.find(C.Value, , xlValues, xlWhole)
            If Not fn Is Nothing Then
                C.Interior.Color = vbYellow
            End If
    Next
   
End Sub
 

Herbalgiraffe

New Member
Joined
Feb 22, 2020
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
VBA Code:
Sub Highlight()

    Dim Ws1 As Range, Ws2 As Range, Wb1 As Workbook, Wb2 As Workbook, C As Range, fn As Range
      
    Workbooks.Open "T:\Customer Service\Site\Pre-Order Skus.xlsx"
     
    Set Wb1 = Workbooks("Test Order File.xlsm")
    Set Ws1 = Wb1.Worksheets("Test").Range("C1", Wb1.Worksheets("Test").Cells(Rows.Count, 3).End(xlUp))
    Set Wb2 = Workbooks("Pre-Order Skus.xlsx")
   'Here is where I tried activating the other workbook to get it to see Ws2.
    Set Ws2 = Wb2.Worksheets("Sheet1").Range("A1", Wb2.Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp))
      
'    Worksheets("Sheet1").Range("A1").Select
  
    For Each C In Ws1
        Set fn = Ws2.find(C.Value, , xlValues, xlWhole)
            If Not fn Is Nothing Then
                C.Interior.Color = vbYellow
            End If
    Next
  
End Sub
That worked perfectly, and I might have smacked my forehead once I realized what the fix was, thank you!
 

Watch MrExcel Video

Forum statistics

Threads
1,119,290
Messages
5,577,218
Members
412,777
Latest member
jmulldome
Top