Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
search within string
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
search within string
=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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
search within string
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
search within string
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
search within string
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
to search for a string and affect data if it finds the string? | Excel Worksheet Functions | |||
search for string within a string | Excel Programming | |||
VBA function : How to search a string in another string? | Excel Programming | |||
Excel XP VBA code to search all macro code in Excel module for specific search string criteria | Excel Programming | |||
Excel XP VBA code to search all macro code in Excel module for specific search string criteria | Excel Programming |