Problem with copying variable(s) to cell(s) and converting strings to mixed case

[Don Glass]

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.

 

[Lance]

Here are some examples that might help


Sub runit()
Dim FulStr As String
Dim Cty As String
Dim St As String
Dim Zip As String
Dim Lngth As Integer
Dim x As Integer

x = 1
FulStr = Trim(Cells(3, 1)) 'cell a3
Lngth = Len(FulStr)
Cty = Application.Proper(Left(FulStr, 19))
'some functions, ie proper, require the applications
object
St = Application.Proper(Mid(FulStr, 21, 2))
Zip = Right(FulStr, 5)

[b3] = Cty 'one assinment style
[c3] = St
Cells(3, 4) = Zip ' heres another
End Sub



Lance

 

[mrmac]

Try this: refer to the cell you are evaluating in the
format:
cells(irow, icolumn)
irow and icolumn are variables, thus when irow = 3 and
icolumn = 1, the cell you are evaluating is "A3". Put it
in a loop, either for..next or Do...Loop, and process the
results.

Example:

Cty = Left(cells(irow, icolumn), 19)

 

[Otto Moehrbach]

Don
Perhaps I can help you. What you presented in your post reminds me of
the old adage about how you eat a cow: One piece at a time. Your post
essentially presents the whole cow in one sitting.
Yes, you can work with a cell without ever selecting that cell. In
fact, you should always try to not select cells, ranges, or sheets. Doing
so just slows things down.
To copy a variable value to a cell, just write:
Range("A1")=MyVarValue
You can refer to a cell in a relative manner so that no cell address is
ever hard-coded.
You would use a looping routine, either a Do loop or a For loop, to do
what you want.
The code you show, using Left, Right, and Mid, to extract the City,
State, and Zip values is hard-coding the length of the City, State, and Zip
values. I know you don't want that. You can use the worksheet
Text-To-Columns feature to do that for you.
If you wish, send me a small file (via email, remove "nospam" from my
address) with a sample of your data as it is when you start and as you want
it when you finish. Just 10 or 20 companies is all that is needed. Your
post is pretty detailed as to what you want so you don't need to provide
more details. I'll work with you to come up with something you can use.
HTH Otto

 

[Don Glass]

Otto,
Thank you for your reply. I did not expect such a quick response on
Sunday morning. I agree that I have several unsolved problems; i.e.
'the cow'. But I felt it was better to try to cover them in one posting
(rather than several postings) so that solutions would be hopefully made
in mind to not conflict, and support the other parts of the desired end
result.
My email address is (e-mail address removed), and if you will email me, I will
reply and send you the sample Excel file for you to work with, otherwise
I could only send you text via this web site.
I can see how your formula for copying a variable to a cell will
work, but how can it be done without hardcoding the cell address? Could
this be accomplished with the Cells function, i.e. Cells(irow, icol),
assigning new values as each new row or column is encountered?

Thanks,

Don G.