I have done programming in other languages, have been wanting to learn
VB and/or VBA, and have recently had very strong reason to program in
VBA. I have Office 2000, so I am therefore using Excel 2000. The
concepts and coding are different than what I am accustomed to, but I
really appreciate how much can be accomplished by relatively little code
compared to other programming languages. However, every language has
its little tricks and nuances, and I have been unable to find a way to
copy a variable value to a cell after several days of research, study,
and attempting a large number of common sense coding approaches.
In my job, I receive data in the format below, and it is vitally
important to place all of the data for each record in the same row for
each company listing so that it can be easily exported to a database
program. Thus the Company name would remain in Column A, the Address
will be in Column B, the City will be in Column C, the State will be in
Column D, and the Zip Code will be in Column E. A sample of the data
format as I receive it is as follows:

This is Column A:

ABC CHEMICAL COMPANY
1500 WHALE ST
SAN DIEGO CA 90012


MAIN STREET PLUMBING
2915 MAIN ST
PASADENA CA 91114


I have no problem in moving or copying a cell's value to any other
cell, so getting the data for a company record in a single row is not my
problem. Also, I have no problem with coding to delete the blank rows.
The problem begins with the fact that City, State, and Zip Code are all
in one cell, and must be separated to fit in the database fields
properly. It is easy to use the Trim, Len, Left, Mid, and Right
functions to yield the values for the variables I declare and assign as
follows:

Dim CurCell As Variant
Dim FulStr As String
Dim Cty As String
Dim St As String
Dim Zip As String
Dim Lngth As Integer

CurCell = Range("A3").Select
FulStr = Trim(CurCell)
Lngth = Len(FulStr)
Cty = Left(ActiveCell, 19)
St = Mid(ActiveCell, 21, 2)
Zip = Right(ActiveCell, 5)

The above code requires that I make the City/St/Zip cell the
ActiveCell in order for the above to yield the correct values. Is there
a way I can code this without making the City/St/Zip cell the
ActiveCell? The biggest problem is that I have been unable to figure
the right combination of Object(s) and Method(s)/ Property(ies) to get
each variable to copy to the appropriate cell.
Another very important piece of this puzzle is that it is vitally
important that there must be no reference to any specific cell, such as
"A3", i.e. hard-coding will not work in this application. The reason is
that this must work all the way down to the end of the file, which could
be any number of records, but is usually around 1,000 records
(companies). I think that it would require a Static integer variable
that increments by one for each record, and would work with the Cells
function. This should all work within a Do Loop while not EOF, don't
you think?
And finally, it is very important to get the case in the text from
all capitals to mixed case, i.e. the first letter of each company name,
street, and city to be capitalized, and the following letters to be
lower case. In my research, I have found a function called 'Proper',
but have been unsuccessful in getting it to work. How can that be coded
to work, or does someone out there have the code for a module that will
accomplish the task of converting to mixed case effectively?
If someone can help me with either or both of these problems, I would
greatly appreciate it. Time is of essence in getting this to work, so
please respond quickly if at all possible.

Don G.



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!