I'm trying to recall the vb syntax to perform the above function,
which will evaluate each row in a column that will have this type of
data:

3180015155 002075

and replace the current data with the last 6 characters like this:

002075

recommendations?

Right, Left or Mid functions, depending on how your state them.

"S Himmelrich" wrote:

> I'm trying to recall the vb syntax to perform the above function,
> which will evaluate each row in a column that will have this type of
> data:
>
> 3180015155 002075
>
> and replace the current data with the last 6 characters like this:
>
> 002075
>
> recommendations?
>

Can you give me an example of the statement that I'd put in VB?

On Dec 6, 4:52 pm, JLGWhiz <JLGW...@discussions.microsoft.com> wrote:
> Right, Left or Mid functions, depending on how your state them.
>
> "S Himmelrich" wrote:
> > I'm trying to recall the vb syntax to perform the above function,
> > which will evaluate each row in a column that will have this type of
> > data:
>
> > 3180015155 002075
>
> > and replace the current data with the last 6 characters like this:
>
> > 002075
>
> > recommendations?

3180015155 002075
>
> > and replace the current data with the last 6 characters like this:
>
> > 002075


Assume the above data is in cell B2 of the ActiveSheet.

myVar = Right(Range("B2").Value, 6) 'myVar returns 002075
Or
myVar = Mid(Range("B2").Value, 12, 6) 'myVar returns 002075

There are more details available in the VBA help files by pressing Alt+F11
and typing [Right Function] or [Mid Function] in the Help search box, then
select the appropriate one from the menu that is displayed.



"S Himmelrich" wrote:

> Can you give me an example of the statement that I'd put in VB?
>
> On Dec 6, 4:52 pm, JLGWhiz <JLGW...@discussions.microsoft.com> wrote:
> > Right, Left or Mid functions, depending on how your state them.
> >
> > "S Himmelrich" wrote:
> > > I'm trying to recall the vb syntax to perform the above function,
> > > which will evaluate each row in a column that will have this type of
> > > data:
> >
> > > 3180015155 002075
> >
> > > and replace the current data with the last 6 characters like this:
> >
> > > 002075
> >
> > > recommendations?
>
>

Here is what I've come up with:

' Convert account column
Columns("B:B").Select
Selection.Value = Right(Range("B2").Value, 6) 'myVar returns
002075

It doesn't work, it duplicates B2 for all cells with last six
characters...how to make it cycle through all records?







On Dec 6, 9:11 pm, JLGWhiz <JLGW...@discussions.microsoft.com> wrote:
> 3180015155 002075
>
>
>
> > > and replace the current data with the last 6 characters like this:
>
> > > 002075
>
> Assume the above data is in cell B2 of the ActiveSheet.
>
> myVar =Right(Range("B2").Value, 6) 'myVar returns 002075
> Or
> myVar = Mid(Range("B2").Value, 12, 6) 'myVar returns 002075
>
> There are more details available in the VBA help files by pressing Alt+F11
> and typing [RightFunction] or [MidFunction] in the Help search box, then
> select the appropriate one from the menu that is displayed.
>
>
>
> "S Himmelrich" wrote:
> > Can you give me an example of the statement that I'd put in VB?
>
> > On Dec 6, 4:52 pm, JLGWhiz <JLGW...@discussions.microsoft.com> wrote:
> > >Right, Left or Mid functions, depending on how your state them.
>
> > > "S Himmelrich" wrote:
> > > > I'm trying to recall the vb syntax to perform the abovefunction,
> > > > which will evaluate each row in a column that will have this type of
> > > > data:
>
> > > > 3180015155 002075
>
> > > > and replace the current data with the last 6 characters like this:
>
> > > > 002075
>
> > > > recommendations?- Hide quoted text -
>
> - Show quoted text -

On Dec 12, 8:40 pm, S Himmelrich <himmelr...@gmail.com> wrote:
> Here is what I've come up with:
>
> ' Convert account column
> Columns("B:B").Select
> Selection.Value = Right(Range("B2").Value, 6) 'myVar returns
> 002075
>
> It doesn't work, it duplicates B2 for all cells with last six
> characters...how to make it cycle through all records?
>
> On Dec 6, 9:11 pm, JLGWhiz <JLGW...@discussions.microsoft.com> wrote:
>
>
>
> > 3180015155 002075
>
> > > > and replace the current data with the last 6 characters like this:
>
> > > > 002075
>
> > Assume the above data is in cell B2 of the ActiveSheet.
>
> > myVar =Right(Range("B2").Value, 6) 'myVar returns 002075
> > Or
> > myVar = Mid(Range("B2").Value, 12, 6) 'myVar returns 002075
>
> > There are more details available in the VBA help files by pressing Alt+F11
> > and typing [RightFunction] or [MidFunction] in the Help search box, then
> > select the appropriate one from the menu that is displayed.
>
> > "S Himmelrich" wrote:
> > > Can you give me an example of the statement that I'd put in VB?
>
> > > On Dec 6, 4:52 pm, JLGWhiz <JLGW...@discussions.microsoft.com> wrote:
> > > >Right, Left or Mid functions, depending on how your state them.
>
> > > > "S Himmelrich" wrote:
> > > > > I'm trying to recall the vb syntax to perform the abovefunction,
> > > > > which will evaluate each row in a column that will have this type of
> > > > > data:
>
> > > > > 3180015155 002075
>
> > > > > and replace the current data with the last 6 characters like this:
>
> > > > > 002075
>
> > > > > recommendations?- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

Sub convert()

If ActiveCell.Value <> 0 Then
Do Until ActiveCell.Value = ""
ActiveCell.Copy
ActiveCell.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
Application.CutCopyMode = False
ActiveCell.Value = Right(ActiveCell, 6)
ActiveCell.Offset(1, -1).Select
Loop
End If
End Sub

This is what i understand from your text. Place the cursor on the
first value and run this code. It will copy the current value into
other cell and trim for the last six right digits. and carry on
working till the active cell is "" or empty.

Hope this will help........

Thanks,


AA