mid$ in VBA

[cliodne]

I have two worksheets of data from automobile license plates. The data
is a string - 7 characters. I have to compare the license plate data
from each worksheet, find the matches and output the ones that match in
a third sheet. I would use an IF statement since that'd be pretty easy,
but my program not only has to find the absolute matches, but also match
ones that match 6 of the characters, 5 of the characters, 4 of the
characters and so on.

I've already set up my ranges from the three sheets. I think the best
way to be able to do this would be using mid$, but it really confuses
me, and I'm having the hardest time finding a website that talks about
how to use it.

If anyone could explain to me mid$ or point me to some useful sites,
I'd be eternally grateful.

Thanks,
Cami

 

[cliodne]

Please, anyone?

If not that, perhaps how to search two data arrays by examing th
contents of the string?

*desperate*
Cam

 

[Nick Hebb]

mid$(txt, start_position, length):

txt: the original string
start_position: starting position in the string that you want to
extract the substring from
length: the number of characters to extract

So, if txt = "VVY 012"

Then,
mid$(txt, 1, 2) = "VV" [start on 1st character, get 2 characters]
mid$(txt, 3, 5) = "Y 012" [start on 3rd character, get 5 characters]

If the length value is greater than the extent of the string, it will
handle it gracefully and just return all the characters from the
start_position to the right. So,
mid$(txt, 3, 10) = "Y 012" just like mid$(txt, 3, 5) did.

If the start position is greater than the length of the string, it will
return an empty string.

HTH,

Nick Hebb
BreezeTree Software
http://www.breezetree.com

 

[cliodne]

Thank you so much Nick - that's helped a lot. Why, when the middle
value is longer than the last value, does it give an empty value? I
have a 7 character string, where I want to look at each character
comaring it to another 7 character string, so my middle value is mostly
always larger than my last. For some reason it's just not working out,
and I think it has to do with my If statement involving mid$ and
getting it to output the matching characters. I've attached my code
below:

Dim LicenseRangeA As Range
Dim LicenseRangeB As Range
Dim LicenseRangeMatched As Range
Dim nrows As Integer
Dim i As Integer
Dim N As Integer
Dim M As Integer
Dim O As Integer
'
' the following is just setting up my ranges (in a sort of round about
way).
'
Sheets("Location A").Select
Range("A2").Select
'
Do While ActiveCell <> ""
N = N + 1
ActiveCell.Offset(1, 0).Select
Loop
'
N = N + 1
'
Sheets("Location B").Select
Range("A2").Select
Do While ActiveCell <> ""
M = M + 1
ActiveCell.Offset(1, 0).Select
Loop
'
M = M + 1
'
'
Sheets("Location A").Select
Set LicenseRangeA = Range("A2:A" & N)
'
Sheets("Location B").Select
Set LicenseRangeB = Range("A2:A" & M)
'
'
' THIS IS THE PROBLEMATIC PART
'
For k = 1 To N
For j = 1 To M
For i = 1 To 7
If Mid$(LicenseRangeA.Cells(k, 1), 1, i) =
Mid$(LicenseRangeB.Cells(j, 1), 1, i) Then
A = A + 1
End If
Next i
Next j
Next k
'
Sheets("Matched").Select
Range("A5").Select
'
i = 0
For i = 1 To 9
If A = 7 Then
ActiveCell.Value = LicenseRangeA.Cells(i, 1)
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "Certain Match"
ActiveCell.Offset(1, -1).Select
ElseIf A <= 4 Then
ActiveCell.Value = LicenseRangeA.Cells(i, 1)
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "No Match"
ActiveCell.Offset(1, -1).Select
Else
ActiveCell.Value = LicenseRangeA.Cells(i, 1)
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "Possible Match"
ActiveCell.Offset(1, -1).Select
End If
Next i
End Sub


Thank you to anyone who can help,
Cami

 

[securityman]

In your if statement try putting .value at the end before the 1,i
and in the other range also.

If Mid$(LicenseRangeA.Cells(k, 1).*value*, 1, i) =
Mid$(LicenseRangeB.Cells(j, 1).*value*, 1, i) Then........

Not sure it will work, but no harm in trying.

 

[Nick Hebb]

Instead of creating complex loops to iterate through the 7 characters,
just create some predefined arrys for each license. The example below
(admittedly untested) looks for matching substrings of length 4, 5, 6,
and 7:

Dim license As String
Dim license_chars(9) As String
Dim i As Integer
Dim firstAddress As Range
Dim RangeA As Range
Dim RangeACell As Range
Dim RangeB As Range
Dim RangeBCell As Range
Dim N As Long:

' hypothetical
N = 100
RangeA = Worksheets(1).Range("A2:A" & N)
RangeB = Worksheets(1).Range("A2:A" & N)


For Each RangeACell In RangeA.Cells

license = RangeACell.Value

license_chars(0) = Mid(license, 1, 4)
license_chars(1) = Mid(license, 2, 4)
license_chars(2) = Mid(license, 3, 4)
license_chars(3) = Mid(license, 4, 4)
license_chars(4) = Mid(license, 1, 5)
license_chars(5) = Mid(license, 2, 5)
license_chars(6) = Mid(license, 3, 5)
license_chars(7) = Mid(license, 1, 6)
license_chars(8) = Mid(license, 2, 6)
license_chars(9) = Mid(license, 1, 7)

With RangeB
For i = LBound(license_chars) To UBound(license_chars)
Set RangeBCell = .Find(license_chars(i), LookIn:=xlValues,
LookAt:=xlPart)
If Not RangeBCell Is Nothing Then
' Match found !!
firstAddress = RangeBCell.Address
' Optionally, keep looking
Do
' Do somehere here with the info
Set RangeBCell = .FindNext(RangeBCell)
Loop While Not RangeBCell Is Nothing And _
RangeBCell.Address <> firstAddress
End If
Next
End With
Next


HTH,

Nick Hebb
BreezeTree Software
http://www.breezetree.com

 

[cliodne]

I have quite a few questions (I'm a beginner, so it took a while to
understand the code).

When setting the ranges in that way with the worksheets, does it have
to be

RangeA = Worksheets("name of worksheet").Range(A1:A10")
RangeB = Worksheets("name of worksheet").Range(A1:A10")

Is having set before the two not required?

Then there is the For loop where it's all the processing for the
different mid's and then the finding, and the match is found. Is the
thirdAddress the range for the third sheet where values will be
output'd? Oh, and then the "optionally, keep looking", means that the
string will continue to be matched with other values in the other range
even if it was already matched. "Do somethign with the info" - is this
where I add the outputting part if the prior firstaddress was not
actually the outputting part. Ack, sorry for the flood of questions!
I can understand most of the code though.

Thanks!

firstAddress = RangeBCell.Address
' Optionally, keep looking
Do
' Do somehere here with the info
Set RangeBCell = .FindNext(RangeBCell)
Loop While Not RangeBCell Is Nothing And _
RangeBCell.Address <> firstAddressEnd If

 

[Nick Hebb]

Yes 'Set' is required for the Range assignments. Like I said, the code
wasn't tested - mainly I was trying to point out that creating an array
of search values then using the Find function may be as easier
approach. The Find function code is almost verbatim out of the help
file.

-- Nick