Hello Dave,
Here is an alternative method if you need to search a large range of cells quickly. The macro examines only cells that contain true strings and loops through each byte of the string. The compare is speeded up by using an API call to place the bytes of the string (cell contents) into an array. The bytes are then compared to the "-" and " " characters. There is a match if the count of spaces and dashes equal the length of string. You would need to insert whatever code you want to run at this point in the macro (its marked in green).
To Use the Macro:
FastCheck "A1:Z300"
Replace "A1:Z300" with the range you want to check
Private Declare Sub CopyMemory _
Lib "Kernel32.dll" _
Alias "RtlMoveMemory" _
(ByRef lpvDest As Any, _
ByRef lpvSource As Any, _
ByVal cbCopy As Long)
Sub FastCheck()
Dim Bytes As Long
Dim ByteArray() As Byte
Dim Cell
Dim MyStr As String
Dim N As Long
For Each Cell In Range("A1:F93")
If VarType(Cell) = 8 Then
MyStr = Cell.Value
Bytes = Len(MyStr) + 1
ReDim Preserve ByteArray(Bytes)
CopyMemory ByteArray(1), ByVal MyStr, Bytes
For Each B In ByteArray
If B = 32 Or B = 45 Then N = N + 1
Next B
If N = Bytes Then
'Data is good - add whatever routines you need here
End If
N = 0
End If
Next Cell
End Sub
Sincerely,
Leith Ross
Bookmarks