Searching a range for IsEmpty()

[Ritzyy]

Hi,

I'm trying to search a range to see if any cell contains a wild card End*

I'm not sure how to make this code work.


Dim cell As Range
Set WS3 = ThisWorkbook.Sheets("Sheet3")
Set WS5 = ThisWorkbook.Sheets("Sheet5")
Set WSH = ThisWorkbook.Sheets("Sheet6")


If IsEmpty(WSH.Cells(WS3.Range("A5") + 1, WS3.Range("C1") + 1)) Then
For Each cell In WS5.Cells(1, 2).Resize(WS3.Range("A5") - WS3.Range("A3"))
If cell Like "End*" Then
'stuff goes here
End If
Next cell
End If

Basically I'm trying to search a range defined by resize to see if any cells contain anything starting with End.

The reason I used resize instead of Range() is because the number of rows to be searched varies with the numbers in A5 and A3 of worksheet 3.

Thanks in advance, I'm relatively new to vba so it's probablly a simple mistake.

 

[hippiehacker]

like this?

Dim cell As Range
Set WS3 = ThisWorkbook.Sheets("Sheet3")
Set WS5 = ThisWorkbook.Sheets("Sheet5")
Set WSH = ThisWorkbook.Sheets("Sheet6")


If IsEmpty(WSH.Cells(WS3.Range("A5") + 1, WS3.Range("C1") + 1)) Then
For Each cell In WS5.Cells(1, 2).Resize(WS3.Range("A5") - WS3.Range("A3"))
If InStr(cell, "End*") = 1 Then
 'code here
End If
Next cell
End If