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