Compare Range to Array

[RgotG]

Hello
I'm trying to compare cell values to an array. As you can see I was able to get the sheet names into the array and now I would like to compare the values in column B against the array. If a cell contains a value that is within the array I would like to return True. In a next step I would than also need the cell details and its value/ string for another step.

The problem seems in the bold line.

Any help would be highly appreciated Thank you

Sub MatchSheet()

Dim xSheet As Worksheet
Dim ArraySheets() As String
Dim x As Variant
'get sheet names into array
For Each xSheet In ActiveWorkbook.Worksheets

ReDim Preserve ArraySheets(x)
ArraySheets(x) = xSheet.Name

x = x + 1
Next xSheet

'loop through range for array values
Dim i As Integer
Dim ListComp As Boolean
For i = 1 To 250
With ActiveWorksheet
For x = LBound(ArraySheets) To UBound(ArraySheets)
If .Cells(i, 2).Value = ArraySheets(x) Then
ListComp = True
End If
If ListComp = True Then
Debug.Print "Test"
End If
Next x
End With
Next i
End Sub

 

[Peter_SSs]

The problem seems in the bold line.

Any help would be highly appreciated Thank you

Sub MatchSheet()

Dim xSheet As Worksheet
Dim ArraySheets() As String
Dim x As Variant
'get sheet names into array
For Each xSheet In ActiveWorkbook.Worksheets

ReDim Preserve ArraySheets(x)
ArraySheets(x) = xSheet.Name

x = x + 1
Next xSheet

'loop through range for array values
Dim i As Integer
Dim ListComp As Boolean
For i = 1 To 250
With ActiveWorksheet
For x = LBound(ArraySheets) To UBound(ArraySheets)
If .Cells(i, 2).Value = ArraySheets(x) Then
ListComp = True
End If
If ListComp = True Then
Debug.Print "Test"
End If
Next x
End With
Next i
End Sub

The problem is actually before that. Instead of ActiveWorksheet it should be ActiveSheet.

You would avoid that sort of inadvertent error if you set the requirement to declare all variables - very good practice. You can do that in the vba window via Tools -> Options -> Editor tab -> Mark 'Require Variable Declaration' -> OK

 

[RgotG]

Thank you so much Peter_SSs

Now I get: Excel VBA, error 438 "object doesn't support this property or method
Same row: If .Cells(i, 2).Value = ArraySheets(x) Then

Any advise?

 

[rlv01]

It runs without that error for me.

 

[Peter_SSs]

Thank you so much Peter_SSs

Now I get: Excel VBA, error 438 "object doesn't support this property or method
Same row: If .Cells(i, 2).Value = ArraySheets(x) Then

Any advise?

1. Please post the full revised code that you are now using. When doing so use Code tags (see my signature block below) to preserve any indentation formatting that you have in your code.

2. What Excel version and what operating system are you using?

 

[Fluff]

How about, something like

Sub MatchSheet()

   Dim Cl As Range
   Dim Ws As Worksheet
   
   With CreateObject("scripting.dictionary")
      For Each Ws In Worksheets
         .Add Ws.Name, Nothing
      Next Ws
      For Each Cl In Range("B1", Range("B" & Rows.Count).End(xlUp))
         If .exists(Cl.Value) Then
            ' do something like
            Sheets(Cl.Value).Range("A1").Copy Cl.Offset(, 1)
         End If
      Next Cl
   End With
End Sub

 

[RgotG]

Thank you for the replies and my apologize for not getting back quicker.
@Peter_SSs below the code and I'm using excel 2016 on Mac

Sub MatchSheet()


Dim xSheet As Worksheet
Dim ArraySheets() As String
Dim x As Variant
'get sheet names into array
For Each xSheet In ActiveWorkbook.Worksheets


ReDim Preserve ArraySheets(x)
ArraySheets(x) = xSheet.Name

x = x + 1
Next xSheet


'loop through range for array values
Dim i As Integer
Dim ListComp As Boolean
Dim cell As Range
cell = ActiveSheet.Range("B:B")
For i = 1 To 250
With ActiveSheet
For Each cell In ArraySheets(x)
ListComp = True
If ListComp = True Then
Debug.Print "Test"
End If
i = i + 1
Next i
End With
End Sub
@Fluff I'll try your suggestion now

 

[Fluff]

My code will not work on a Mac

 

[RgotG]

Haha - thanks for the quick reply. Just learned it the hard way and started searching whether I did a typo

I'll go back to my array attempt

 

[RgotG]

@Peter_SSs @Fluff
Below my solution. Works for me know. I currently have 3 worksheets that have a name that is also a value in the second column on my sheet (B:B) and the macro currently prints success 3 times -- seems to work.

I'll try to replace the debug.print with the actual action that the code should do. Hope that will work

Thank you for all your help. I'll let you know how it goes

Sub MatchSheet()


Dim xSheet As Worksheet
Dim ArraySheets() As String
Dim x As Variant
'get sheet names into array
For Each xSheet In ActiveWorkbook.Worksheets


ReDim Preserve ArraySheets(x)
ArraySheets(x) = xSheet.Name

x = x + 1
Next xSheet


'loop through range for array values
Dim i As Variant
Dim vCell As Range
For Each vCell In ActiveSheet.Range("B1:B250").Cells
For Each i In ArraySheets
If StrComp(vCell.Value, i, vbTextCompare) = 0 Then
Debug.Print "success" 'test
Exit For
End If
Next i
Next vCell


End Sub