PDA

View Full Version : Solved: Cutting out the five right letters from ActiveCell



DanOfEarth
12-26-2008, 02:55 PM
Hi,

I'm trying to write a snippet that will cut the five right letters/numbers out of the Activecell (an address zip code below) so I can paste it to the next cell. Then trim the extra space. I haven't worked with VBA in eight years.

Mcallen, TX 78503

I've tried versions of Right(), Mid (), Parse(), RTrim....

I know it's got to be simple.

Thanks.

mikerickson
12-26-2008, 03:49 PM
zipCode=Right(cityStateZip, 5)
alternatly, if the City State Zip is in cell A1 you could put
=RIGHT(A1,5)
in B1

DanOfEarth
12-26-2008, 04:34 PM
Thanks Mike,

I know that would work on a static worksheet, however I'm attaching a series of subs to a Command Button.

In my sub-procedure, I'm trying to in exact order:
1st: Activate the cell with the below info in it.

Mcallen, TX 78503

2nd: parse the information in the cell by programatically cutting the Zip out of the cell while leaving the City in the cell
3rd: then paste the Zip into a different cell.

Paul_Hossler
12-26-2008, 05:26 PM
One way, not tested and could use error checking


Sub GetZip(AddressCell As Range, ZipCell As Range)

'only use first cell if more that one passed
'pull out zip code
ZipCell.Cells(1, 1).Value = Right(AddressCell.Cells(1, 1).Value, 5)
'put in the left chars - 6 (zip 5 + 1 for space)
AddressCell.Cells(1, 1).Value = Left(AddressCell.Cells(1, 1).Value, Len((AddressCell.Cells(1, 1).Value) - 6))

End Sub


Paul

DanOfEarth
12-26-2008, 06:00 PM
Paul,

That looks like it. If not, it's close.

However what would the syntax look like if the starting cell is the "Activecell". The reason for this is that the cell with the info in it is non-static. (the user is going to activate it first).

Would I replace:

ZipCell.Cells(1, 1).Value = Right(AddressCell.Cells(1, 1).Value, 5)

with

ZipCell.Activecell.Value = Right(AddressCell.Cells(1, 1).Value, 5)

I'm trying it in a minute but I suspect it'll be a syntax error.

Paul_Hossler
12-26-2008, 06:20 PM
From what I understood, you wanted to put the zip in another cell, and leave the original (ActiveCell) "zip-less" when you ran the sub from a command button

In the command button code you could always call my earlier sub (or embed it)


Call GetZip(ActiveCell, ActiveCell.Offset(0,1))

or embedded

With ActiveCell
.Offset(0, 1).Value = Right(.Value, 5)
.Value = Left(.Value, Len((.Value) - 6))
End With



which would put the zip one cell to the right

SOMEHOW you have to specifiy the second cell to receive the zip (if that's what you want)

Again, NOT tested and barely proof-read

Paul

mikerickson
12-26-2008, 06:45 PM
If the sequence is
1)User selects a cell (containing "Davis CA 95616")
2)User presses button
3)ActiveCell gets "Davis CA", the cell to the right gets "95616"

I would use this code.
Sub SplitOffZipFromActiveCell()
Dim zipStr As String

With ActiveCell
zipStr = Right(CStr(.Value), 5)
If zipStr Like "[0-9][0-9][0-9][0-9][0-9]" Then
.Offset(0, 1) = zipStr
.Value = Left(.Value, Len(.Value) - 5)
Else
MsgBox "Cell does not end in zipcode"
End If
End With

End SubThe checking for a zip code is there because users sometimes double press Command Buttons, plus they press the wrong button when wrong cells are selected.

This code only works with 5 digit zip codes, it does not detect/handle 9 digit zips.

DanOfEarth
12-27-2008, 07:05 AM
OK,

I combined them both, and even added a second handler for the state as well, but it keeps flagging the "else" error handler. It was trying to parse the following into three columns:

Houston, TX 77009

Here's the code:


Dim zipStr As String
Dim stateStr As String
With ActiveCell
zipStr = Right(CStr(.Value), 5)
If zipStr Like "[0-9][0-9][0-9][0-9][0-9]" Then
.Offset(0, 3) = zipStr
.Value = Left(.Value, Len(.Value) - 6)
Else
MsgBox "Cell does not end in zipcode"
End If
End With
With ActiveCell
stateStr = Right(CStr(.Value), 2)
If zipStr Like "[A-Z][A-Z]" Then
.Offset(0, 2) = stateStr
.Value = Left(.Value, Len(.Value) - 3)
End If
End With

Is it possibly flagging the syntax for the CStr? I've never used that before.

Edit Lucas: VBA Tags added to code

mikerickson
12-27-2008, 07:21 AM
Try changing this to
zipString = Right(Trim(CStr(.Value)), 5)In case there are trailin spaces in the original data.

The CStr converts to a string value and is there in case the user presses the button while there is an error value (eg #DIV/0) in the Active Cell.

DanOfEarth
12-27-2008, 07:48 AM
GOT it!! You guys rock!:ipray:

(I hate smileys but that was too tempting)

On a hunch, I "commented out" the if/else statements to see if there was an issue, and it hit it. Luckily the data I'm importing is in exact format, because there's no error checking on this, but it'll do for this task. Here's the code that worked.


With ActiveCell
zipStr = Right(CStr(.Value), 5)
.Offset(0, 3) = zipStr
.Value = Left(.Value, Len(.Value) - 6)
End With
With ActiveCell
stateStr = Right(CStr(.Value), 2)
.Offset(0, 2) = stateStr
.Value = Left(.Value, Len(.Value) - 4)
End With

I've found a new home in a forum! Hopefully I'll be able to return the favor.


Edit Lucas: VBA Tags added to code

lucas
12-27-2008, 09:59 AM
Hi Dan, Welcome to the forum. When you post code, select it and hit the vba button and your code will be formatted for the forum.

DanOfEarth
12-27-2008, 10:28 AM
That's how ya'll did that. Thanks again. I'll be hanging out alot here.