Byte Comparison for Strings

[jwise]

My data is collected with many spelling variations. This is an attempt to fix problems associated with these spellings. I have two strings. One is the "hunt" data (what I'm looking for) and the second is the content of a particular cell (gathered from multiple sources). I'm basically going to see if this cell contains the "essential" characters in my "hunt" data. I can not get around VBA error '13' (Run time error- Type mismatch. Here is the relevant code:

[vba] Dim arr_h(30) as Byte
Dim hunt as String
Dim len_hunt as Integer

hunt = "GRDSXWAGE"
len_hunt = Len(hunt)

For j = 1 To len_hunt
arr_h(j) = CByte(MidB(hunt, j, 1))
Next j [/vba]

The error occurs on the next to last statement. I am trying to create two indexable arrays so I can do a byte-by-byte compare looking for my "essential" characters. Any ideas on how I can define the data? I have tried this without "CByte" as well.

[RichardSchollar]

Hi

VBA internally stores strings in unicode. If you want to assign a string to a byte array and you want the character set omitted (0 for standard ANSI) then:

Dim b() As Byte, i As Long
Dim hunt As String
hunt = "GRDSXWAGE"
b = StrConv(hunt,vbFromUnicode)
For i = LBound(b) To UBound(b)
  Debug.Print b(i) & " ";
Next i

Richard

[jwise]

Thanks Richard.

I really appreciate your assistance. The many and varied spellings for these "categories" was driving me up the wall. With this routine, Excel can search to find the "essential spelling" instead of the exact.

I have included the entire source. I invite your comments.

[vba] Function Find_Cat(wk1 As Worksheet, hunt As String) As Integer
'
' 04/16/09
'
' Find_Cat Given a data worksheet(wk1) and a
' category(hunt), Find_Cat searches the worksheet
' and returns the row number. If not found, the
' row number is zero.
'
' This code essentially allows many spelling
' variations to the categories.
'
' This was my idea, but I couldn't figure out how
' to get the data in a byte array. Richard Schollar
' of VBAX answered my query on how to do this.
'
' http://www.vbaexpress.com/forum/showthread.php?t=19052
'

Dim str1 As String

Dim i As Long

Dim len_hunt As Integer
Dim len_text As Integer
Dim j As Integer
Dim k As Integer
Dim l As Integer
Dim m As Integer
Dim row As Integer
Dim found As Integer

Dim arr_h() As Byte
Dim arr_d() As Byte

len_hunt = Len(hunt)

arr_h = StrConv(hunt, vbFromUnicode)

For i = 1 To GetLastRowWithData(wk1)

len_text = Len(wk1.Cells(i, 1))

arr_d = StrConv(wk1.Cells(i, 1), vbFromUnicode)

l = 0
m = 0
found = 0
row = 0

For j = 0 To len_text - 1

If arr_h(l) = arr_d(m) Then
l = l + 1
found = found + 1
End If

m = m + 1

If found = len_hunt Then
row = i
Exit For
End If

Next j
If row <> 0 Then Exit For
Next i

Find_Cat = row

End Function [/vba]

Some notes: The text I'm searching for is in column "A" of the worksheet. The function returns the row number since I am actually searching for data on this row.


I thought this might be interesting to others since simple spelling/wording issues can cause many problems. This will not solve them all, but it does overcome a lot of spelling/wording problems. For example, assume I am looking for "Grounds and Maintenance Wages", and this is captured as "Grounds & Maintenance Wages". The compare will not work. If I condense this to "GRDSWAGE", and search the text for these characters in order, then I can find the data. My data is coming from several sources, and I have no control over these spellings.


GetLastRowWithData returns the number of rows on a worksheet. In my case, this number varies on just about every data sheet.

[jwise]

Sorry. As soon as I show my code, I spot a couple of miscues.

[vba]
Function Find_Cat(wk1 As Worksheet, hunt As String) As Integer
'
' 04/16/09
'
' Find_Cat Given a data worksheet(wk1) and a
' category(hunt), Find_Cat searches the worksheet
' and returns the row number. If not found, the
' row number is zero.
'
' This code essentially allows many spelling
' variations to the categories.
'
' This was my idea, but I couldn't figure out how
' to get the data in a byte array. Richard Schollar
' of VBAX answered my query on how to do this.
'
' http://www.vbaexpress.com/forum/showthread.php?t=19052
'

Dim i As Long

Dim len_hunt As Integer
Dim len_text As Integer
Dim j As Integer
Dim k As Integer
Dim l As Integer
Dim m As Integer
Dim row As Integer
Dim found As Integer

Dim arr_h() As Byte
Dim arr_d() As Byte

len_hunt = Len(hunt)

arr_h = StrConv(hunt, vbFromUnicode)

row = 0

For i = 1 To GetLastRowWithData(wk1)

len_text = Len(wk1.Cells(i, 1))

arr_d = StrConv(wk1.Cells(i, 1), vbFromUnicode)

l = 0
m = 0
found = 0

For j = 0 To len_text - 1

If arr_h(l) = arr_d(m) Then
l = l + 1
found = found + 1
End If

m = m + 1

If found = len_hunt Then
row = i
Exit For
End If

Next j
If row <> 0 Then Exit For
Next i

Find_Cat = row

End Function [/vba]

Hopefully, this is correct!