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
-----Original Message-----
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!
.