I'd like to see if a cell contains only "-" and " " (space) characters.
So I'd like to identify " -----" or " - - - - ", but not " --a- ".
I figure I could look one character at a time for something other than
" " or "-". But this seems tedious. Is there an easier way? Thanks.

=LEN(SUBSTITUTE(SUBSTITUTE(A2,"-","")," ",""))=0

If TRUE it only contains those characters

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dave B" wrote in message
oups.com...
I'd like to see if a cell contains only "-" and " " (space) characters.
So I'd like to identify " -----" or " - - - - ", but not " --a- ".
I figure I could look one character at a time for something other than
" " or "-". But this seems tedious. Is there an easier way? Thanks.

Hello Dave,

Here is an alternative method if you need to search a large range o
cells quickly. The macro examines only cells that contain true string
and loops through each byte of the string. The compare is speeded up b
using an API call to place the bytes of the string (cell contents) int
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 o
string. You would need to insert whatever code you want to run at thi
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


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

--
Leith Ros
-----------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...fo&userid=1846
View this thread: http://www.excelforum.com/showthread.php?threadid=47870

Lightly tested, but code like

Sub ABC()
Dim s As Single, e As Single
sStr = "=SUM(IF(LEN(SUBSTITUTE(SUBSTITUTE($A$2:$F$29, ""-"",""""),""
"",""""))=0,1,0))"
s = Timer
For i = 1 To 10000
res = Application.Evaluate(sStr)
Next
e = Timer
Debug.Print e - s
End Sub

seemed to be fairly consistent regardless of the content of the cells.

Your code was 1/3rd faster with only 3 cells containing text, but when all
cells contained text (17 characters), it was about 2.5 times slower.

You don't need to use the API to create a byte array from a string. Also,
this uses special cells to eliminate looping through all cells.

for the range with only 3 text cells, it was 5 times faster than your
original. For the range with all text strings, it was slightly faster (about
2 seconds on 10000 iterations). Recognize that time only has about 1 sec
resolution, so don't offer these as definitive, but as orders of magnitude.

Sub FasterCheck()

Dim Bytes As Long
Dim ByteArray() As Byte
Dim Cell
Dim MyStr As String
Dim N As Long
Dim s As Single
Dim e As Single, k As Long
s = Timer
For k = 1 To 10000
For Each Cell In Range("A2:F29").SpecialCells(xlConstants, _
xlTextValues)
MyStr = Cell.Value
Bytes = Len(MyStr) + 1
ByteArray = MyStr

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
Next Cell
Next k
e = Timer
Debug.Print e - s
End Sub

--
Regards,
Tom Ogilvy



"Leith Ross" wrote
in message ...

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


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


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile:
http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=478704

Hello Tom,

I appreciate you taking the time do a benchmark test. I stan
corrected on my code being faster. I'll add your other tips and change
to my toolbox.

Thanks,
Leith Ros

--
Leith Ros
-----------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...fo&userid=1846
View this thread: http://www.excelforum.com/showthread.php?threadid=47870