Determining Number of Occurances of X in a variable

[LA Lawyer]

I have a variable that contains a series of addresses uniquely separated by
two lines. I want to determine how many addresses I have without printing
them out.

The contains of the variable would look like:

Mr. Joe Smith
123 Anywhere Street
Anywhere, USA

Ms Jane Smith
456 Penny Lane
Springfield, USA

etc.

 

[DaveLett]

Hi,
You can use the end of line character, presuming that it's unique. For
example, if you have something like the following:

Dim sVar As String

sVar = "Mr.Joe Smith" & vbCrLf & _
"123 Anywhere Street" & vbCrLf & _
"Anywhere , USA" & vbCrLf & _
vbCrLf & _
"Ms Jane Smith" & vbCrLf & _
"456 Penny Lane" & vbCrLf & _
"Springfield , USA" & vbCrLf & _
vbCrLf

Debug.Print (Len(sVar) - Len(Replace(sVar, vbCrLf, ""))) / 8


So, why divide by 8 in the last line? That's because vbCrLf is actually TWO
characters. If your end of line character is only one, then you can change
that value to 4.

 

[Fumei2 via OfficeKB.com]

And as a possible alternative:

Sub NextTry()
Dim sVar As String
Dim next_point As Long
Dim counter As Long

sVar = "Mr.Joe Smith" & vbCrLf & _
"123 Anywhere Street" & vbCrLf & _
"Anywhere , USA" & vbCrLf & _
vbCrLf & _
"Ms Jane Smith" & vbCrLf & _
"456 Penny Lane" & vbCrLf & _
"Springfield , USA" & vbCrLf & _
vbCrLf & _
"Mr.Joe Smith" & vbCrLf & _
"123 Anywhere Street" & vbCrLf & _
"Anywhere , USA" & vbCrLf & _
vbCrLf & _
"Ms Jane Smith" & vbCrLf & _
"456 Penny Lane" & vbCrLf & _
"Springfield , USA" & vbCrLf & _
vbCrLf

next_point = 1
Do Until next_point = 0
next_point = InStr(next_point + 1, sVar, vbCrLf & vbCrLf)
counter = counter + 1
Loop
MsgBox "There are " & counter - 1 & " address chunks."
End Sub

I must say that having a string variable like that seems odd.

Gerry

 

[Fumei2 via OfficeKB.com]

Also, to reiterate what Dave mentioned, it is crucial to know WHAT is being
used as a line-feed. vbLf or vbCrLf. That double character of vbCrLf could
mess things up. Also...will it be consistent throughout the entire variable?

And as a possible alternative:

Sub NextTry()
Dim sVar As String
Dim next_point As Long
Dim counter As Long

sVar = "Mr.Joe Smith" & vbCrLf & _
"123 Anywhere Street" & vbCrLf & _
"Anywhere , USA" & vbCrLf & _
vbCrLf & _
"Ms Jane Smith" & vbCrLf & _
"456 Penny Lane" & vbCrLf & _
"Springfield , USA" & vbCrLf & _
vbCrLf & _
"Mr.Joe Smith" & vbCrLf & _
"123 Anywhere Street" & vbCrLf & _
"Anywhere , USA" & vbCrLf & _
vbCrLf & _
"Ms Jane Smith" & vbCrLf & _
"456 Penny Lane" & vbCrLf & _
"Springfield , USA" & vbCrLf & _
vbCrLf

next_point = 1
Do Until next_point = 0
next_point = InStr(next_point + 1, sVar, vbCrLf & vbCrLf)
counter = counter + 1
Loop
MsgBox "There are " & counter - 1 & " address chunks."
End Sub

I must say that having a string variable like that seems odd.

Gerry

Hi,
You can use the end of line character, presuming that it's unique. For

[quoted text clipped - 16 lines]

characters. If your end of line character is only one, then you can change
that value to 4.