Declaration Issue

[Herbalgiraffe]

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?

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

 

[mohadin]

Dim Ws1 As Range, Ws2 As Range,

Should be

Dim Ws1 As Worksheets
Dim Ws2 As Worksheets

 

[mohadin]

Nop

Dim Ws1 ,ws2 As long

 

[Peter_SSs]

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

    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))

 

[Herbalgiraffe]

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

    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?

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

 

[Fluff]

Are the values in col C of both workbooks?

 

[Herbalgiraffe]

Are the values in col C of both workbooks?

No, the list in Ws2 is in column A.

 

[Fluff]

Ok, how about

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]

Ok, how about

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!

 

[Fluff]

Glad we could help & thanks for the feedback.