Prev: HTML table too big to fit on page is lost
Next: Auto Number
From: LA Lawyer on 30 Apr 2010 12:30
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.


From: DaveLett on 30 Apr 2010 14:04
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.
From: Fumei2 via OfficeKB.com on 30 Apr 2010 18:21
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

DaveLett wrote:
>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.

--
Gerry

Message posted via http://www.officekb.com

From: Fumei2 via OfficeKB.com on 30 Apr 2010 18:23
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?

Fumei2 wrote:
>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.
>

--
Gerry

Message posted via http://www.officekb.com

 | 
Pages: 1
Prev: HTML table too big to fit on page is lost
Next: Auto Number