Anyone know how to use the right function with macros?

jeremy_mattick

New Member
Joined
Jul 27, 2004
Messages
2
I am Trying to create a macro that will select the last 5 characters in a cell

Example (Miama, FL 32565) and then cut and paste these characters into a column labeled zip code. I cannot figure this one out!
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Hello and welcome to the board,

Code:
Sub last_5()
Range("D1").Value = Right(Range("B1").Value, 5)
End Sub

Change cell refs as required. I guess this isn't exactly what you want, do you have a long list?
 
Upvote 0
Hi Jeremy, welcome to MrExcel!

You could try something like this, although there may be a better way,


<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>
<SPAN style="color:#00007F">Sub</SPAN> testeroo()
    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range, _
        cel <SPAN style="color:#00007F">As</SPAN> Range
    <SPAN style="color:#00007F">Set</SPAN> rng = [A1:A20] <SPAN style="color:#007F00">'change range to suit</SPAN>
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cel <SPAN style="color:#00007F">In</SPAN> rng
            <SPAN style="color:#007F00">' the offset will put the zip code in the column</SPAN>
            <SPAN style="color:#007F00">' directly to the right of your rng.</SPAN>
        cel.Offset(, 1).Value = Right(cel.Value, 5)
        cel.Value = Left(cel.Value, Len(cel.Value) - 5)
    <SPAN style="color:#00007F">Next</SPAN> cel
    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>
 
Upvote 0
My macro will not put the formula in there, but just the value. Do you want to leave all of the information in col A as is? And do you wand a formula, or just the value in B?
 
Upvote 0
Great Code firefytr. It worked liek a charm. The only thing is, I need it to be relative, not absolute cell reference. It keeps going back to the specific cell and taking away the last 5 characters. If you could help me with that, that would be awesome. Also, there is an error coming up with this statement.

cel.Value = Left(cel.Value, Len(cel.Value) - 5) I dont know what it is referencing
 
Upvote 0
Are you running it more than once? How will your data look like, is it always the same? And what exactly are you doing, in regards to steps taken when running code?

So would you rather just leave the entire thing in col A, and just have the formula in col B, or values?
 
Upvote 0
there is an error coming up with this statement.

cel.Value = Left(cel.Value, Len(cel.Value) - 5) I dont know what it is referencing
You are probably getting the following error message:
Run-time error 5:
Invalid procedure call or argument

Cause:
The range is set for A1:A20, but your actual test data is less than that i.e. you have blank cells in A1:A20.

Fix:
Option Explicit
Sub testeroo()
Application.ScreenUpdating = False
Dim rng As Range, _
cel As Range
Set rng = [A1:A20] 'change range to suit
For Each cel In rng
' the offset will put the zip code in the column
' directly to the right of your rng.
If cel.Value <> "" Then
cel.Offset(, 1).Value = Right(cel.Value, 5)
cel.Value = Left(cel.Value, Len(cel.Value) - 5)
End If
Next cel
Application.ScreenUpdating = True
End Sub

Regards,
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top