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

 

[RgotG]

Solved it now!!! @Fluff @Peter_SSs
Thank you for taking the time and helping. Below is my solution for what I wanted to achieve.


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 FindRow2 As Range
Dim FindValue As Long
Dim PastRow As Range
Dim PastValue As Long
Dim cell1f As Range 'define first cell of range to find
Dim cell2f As Range 'define last cell of range to find
Dim cellAp As Range 'define first cell of range to past
Dim cellBp As Range 'define last cell of range to past
Dim Con As Object
Set Con = Sheets("NameNameName")
Dim vCell As Range
For Each vCell In ActiveSheet.Range("B1:B250").Cells
For Each i In ArraySheets
' compare column b vs array
If StrComp(vCell.Text, i, vbTextCompare) = 0 Then
' find range to copy
Set FindRow2 = Sheets(vCell.Text).Range("B:B").Find(What:="#####", LookIn:=xlValues)
FindValue = FindRow2.Row
Set cell1f = Sheets(vCell.Text).Cells(FindValue, 4)
Set cell2f = Sheets(vCell.Text).Cells(FindValue, 17)
Sheets(vCell.Text).Range(cell1f, cell2f).Copy
' set range where values should be past
Set PastRow = Con.Range("B:B").Find(What:=vCell.Text, LookIn:=xlValues)
PastValue = PastRow.Row
Set cellAp = Con.Cells(PastValue, 4)
Set cellBp = Con.Cells(PastValue, 17)
Con.Range(cellAp, cellBp).PasteSpecial xlPasteValues
Exit For
End If
Next i
Next vCell
End Sub

 

[Peter_SSs]

... I'm ... on Mac

I had my suspicions about that, hence my question. I don't use a Mac so have no way to test code on that system.
There are Mac user helpers on the forum but the majority use Windows systems so it would be wise to state you are a Mac user for any further questions you have.

Solved it now!!!

That's the main thing! Glad you were able to figure it out.