Prev: Excel 2007 - VBA Password Input shows after Excel is closed
Next: filtering two pieces of data
From: Jim Berglund on 2 Jun 2010 11:23
....or what if we entered the city from an input box, determined the length
of the name and counted back from the comma?
Jim


"Jim Berglund" <jazzzbo(a)shaw.ca> wrote in message
news:OOmseHmALHA.4388(a)TK2MSFTNGP04.phx.gbl...
> How about if the city is entered through an input box as the string
> variable "City"? Each user will be located in a different city, and each
> time a list is being parsed, it will only contain records for that city.
>
> Jim
>
>
> "Ron Rosenfeld" <ron(a)nospam.net> wrote in message
> news:smfc065eimcida33tfuqs61d82lmrhkdn4(a)4ax.com...
>> On Wed, 2 Jun 2010 00:14:10 -0600, "Jim Berglund" <jazzzbo(a)shaw.ca>
>> wrote:
>>
>>>Thanks again, Ron.
>>
>> You're welcome. Glad to help.
>>
>>>By the way, if the city is a variable, City, and not a selection from a
>>>list
>>>of known cities, could this be handled?
>>
>> You will need to determine a rule that unambiguously differentiates
>> the City from the rest of the string.
>>
>> Unless you can do that, you will need a list.
>>
>> In the examples you've provided in the past, plus knowing that some
>> cities consist of more than one word, there seems to be too much
>> variability to devise a rule.
>>
>
>
From: Ron Rosenfeld on 2 Jun 2010 19:20
On Wed, 2 Jun 2010 09:23:43 -0600, "Jim Berglund" <jazzzbo(a)shaw.ca>
wrote:

>...or what if we entered the city from an input box, determined the length
>of the name and counted back from the comma?
>Jim
>
>

If you can control how data is input, then you don't need any of the
parsing routines. Merely use a user form to input the data into the
appropriate form fields.
From: Jim Berglund on 3 Jun 2010 01:58
Sorry, Ron. Let me go back. The application is to manage the impact of Do
Not Call Lists. The data changes daily. We typically work from reverse
phone directories, which list all the numbers in an area code. I I'm trying
to create a list of callable numbers along with whatever contact information
is available in the Reverse Phone Books.

The data in the RPB's is not consistent - but it is consistent enough that
the formula you created works for the data for Calgary. To be generically
useful, I need to be able to allow users to just input the city for their
Area Code. The rest of the data is available...

So, I can use a variable, or it will also work if we remove the reference to
the city - totally. Either will be fine.

I'd like to use a variable - if only to see how it's handled. But if it
can't be, would you please redo the formula and remove the city reference?

Thanks again
Jim


"Ron Rosenfeld" <ron(a)nospam.net> wrote in message
news:5opd06dsul7s5le7sbntflj1b2mat3cbt3(a)4ax.com...
> On Wed, 2 Jun 2010 09:23:43 -0600, "Jim Berglund" <jazzzbo(a)shaw.ca>
> wrote:
>
>>...or what if we entered the city from an input box, determined the length
>>of the name and counted back from the comma?
>>Jim
>>
>>
>
> If you can control how data is input, then you don't need any of the
> parsing routines. Merely use a user form to input the data into the
> appropriate form fields.

From: Ron Rosenfeld on 3 Jun 2010 06:36
On Wed, 2 Jun 2010 23:58:33 -0600, "Jim Berglund" <jazzzbo(a)shaw.ca>
wrote:

>Sorry, Ron. Let me go back. The application is to manage the impact of Do
>Not Call Lists. The data changes daily. We typically work from reverse
>phone directories, which list all the numbers in an area code. I I'm trying
>to create a list of callable numbers along with whatever contact information
>is available in the Reverse Phone Books.
>
>The data in the RPB's is not consistent - but it is consistent enough that
>the formula you created works for the data for Calgary. To be generically
>useful, I need to be able to allow users to just input the city for their
>Area Code. The rest of the data is available...
>
>So, I can use a variable, or it will also work if we remove the reference to
>the city - totally. Either will be fine.
>
>I'd like to use a variable - if only to see how it's handled. But if it
>can't be, would you please redo the formula and remove the city reference?
>
>Thanks again
>Jim

Well, you could input a city name and incorporate it into the pattern.

e.g.:

==================================
Option Explicit
Sub ParseAddr()
Dim myRegExp As Object, myMatches As Object
Dim rg As Range, c As Range
Dim i As Long
Dim sCity As String

sCity = InputBox("Name of City: ")

Set rg = Range("A1") 'first cell in column
Set rg = Range(rg, Cells(Cells.Rows.Count, rg.Column).End(xlUp))
rg.Offset(0, 1).Resize(columnsize:=7).ClearContents

Set myRegExp = CreateObject("vbscript.regexp")
myRegExp.ignorecase = True
myRegExp.Pattern = _
"^(\D+?)\s(\D*)\s*(.*)\s(" & sCity & "),?" _
& "\s+([A-Z]{2})\s+(\w+)\s+(\(\d{3}\)\s+\d{3}-\d{4})"

For Each c In rg
If myRegExp.test(c.Text) = True Then
Set myMatches = myRegExp.Execute(c.Text)
For i = 0 To myMatches(0).submatches.Count - 1
c.Offset(0, i + 1) = myMatches(0).submatches(i)
Next i
End If
Next c
End Sub
==================================

It would then only process those lines that had the city name
"Calgary".
From: Jim Berglund on 3 Jun 2010 11:49
Exactly! That's perfect.
Thanks so much for staying with me on this one, Ron. I can now substitute
"SAN FRANCISCO" and it works. I'm delighted.
Jim

"Ron Rosenfeld" <ron(a)nospam.net> wrote in message
news:v81f06t1mnl0i5b4fehat3tc1hmamt95ch(a)4ax.com...
> On Wed, 2 Jun 2010 23:58:33 -0600, "Jim Berglund" <jazzzbo(a)shaw.ca>
> wrote:
>
>>Sorry, Ron. Let me go back. The application is to manage the impact of Do
>>Not Call Lists. The data changes daily. We typically work from reverse
>>phone directories, which list all the numbers in an area code. I I'm
>>trying
>>to create a list of callable numbers along with whatever contact
>>information
>>is available in the Reverse Phone Books.
>>
>>The data in the RPB's is not consistent - but it is consistent enough that
>>the formula you created works for the data for Calgary. To be generically
>>useful, I need to be able to allow users to just input the city for their
>>Area Code. The rest of the data is available...
>>
>>So, I can use a variable, or it will also work if we remove the reference
>>to
>>the city - totally. Either will be fine.
>>
>>I'd like to use a variable - if only to see how it's handled. But if it
>>can't be, would you please redo the formula and remove the city reference?
>>
>>Thanks again
>>Jim
>
> Well, you could input a city name and incorporate it into the pattern.
>
> e.g.:
>
> ==================================
> Option Explicit
> Sub ParseAddr()
> Dim myRegExp As Object, myMatches As Object
> Dim rg As Range, c As Range
> Dim i As Long
> Dim sCity As String
>
> sCity = InputBox("Name of City: ")
>
> Set rg = Range("A1") 'first cell in column
> Set rg = Range(rg, Cells(Cells.Rows.Count, rg.Column).End(xlUp))
> rg.Offset(0, 1).Resize(columnsize:=7).ClearContents
>
> Set myRegExp = CreateObject("vbscript.regexp")
> myRegExp.ignorecase = True
> myRegExp.Pattern = _
> "^(\D+?)\s(\D*)\s*(.*)\s(" & sCity & "),?" _
> & "\s+([A-Z]{2})\s+(\w+)\s+(\(\d{3}\)\s+\d{3}-\d{4})"
>
> For Each c In rg
> If myRegExp.test(c.Text) = True Then
> Set myMatches = myRegExp.Execute(c.Text)
> For i = 0 To myMatches(0).submatches.Count - 1
> c.Offset(0, i + 1) = myMatches(0).submatches(i)
> Next i
> End If
> Next c
> End Sub
> ==================================
>
> It would then only process those lines that had the city name
> "Calgary".

First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Excel 2007 - VBA Password Input shows after Excel is closed
Next: filtering two pieces of data