Without looping, in VBA, how would I take each numeric value in column
A and change it's value to get only the right-most digit?
Thanks,
Sheldon

If all your values in the column are numeric, then you probably want this...

=--RIGHT(A1)

If there can be non-numeric data in your columns, then you probably want
this instead...

=IF(ISNUMBER(A1),--RIGHT(A1),"")

Rick


"Sheldon Potolsky" wrote in message
...
Without looping, in VBA, how would I take each numeric value in column
A and change it's value to get only the right-most digit?
Thanks,
Sheldon

Rick, I tried what you suggested but preceded it with the line
Columns("A:A").Select
in my VBA code
I then typed in
=--RIGHT(A1)
but that line gave me a compile error so I must be missing something -
Expected: Line number or label or statement or end of statement

I never saw the -- syntax before so perhaps I'm using it incorrectly?

Thanks, Sheldon


=--RIGHT(A1)

If there can be non-numeric data in your columns, then you probably want
this instead...

=IF(ISNUMBER(A1),--RIGHT(A1),"")

Rick

"Sheldon Potolsky" wrote in message

...



Without looping, in VBA, how would I take each numeric value in column
A and change it's value to get only the right-most digit?
Thanks,
Sheldon- Hide quoted text -

- Hide quoted text -

Rick was giving you worksheet formulas, not VBA formulas.

Why do you not want to loop?

You could read the range into a variant array, process the contents of the
array (looping, of course), then dump the array back into the sheet.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Sheldon Potolsky" wrote in message
...
Rick, I tried what you suggested but preceded it with the line
Columns("A:A").Select
in my VBA code
I then typed in
=--RIGHT(A1)
but that line gave me a compile error so I must be missing something -
Expected: Line number or label or statement or end of statement

I never saw the -- syntax before so perhaps I'm using it incorrectly?

Thanks, Sheldon


=--RIGHT(A1)

If there can be non-numeric data in your columns, then you probably want
this instead...

=IF(ISNUMBER(A1),--RIGHT(A1),"")

Rick

"Sheldon Potolsky" wrote in message

...



Without looping, in VBA, how would I take each numeric value in column
A and change it's value to get only the right-most digit?
Thanks,
Sheldon- Hide quoted text -

- Hide quoted text -

Actually, that line I gave you was a worksheet formula and not VBA code.
Just put the formula in Row 1 of the column you want the right-most digit in
and copy it down. If you still want to do it via VBA code, then you will
have to loop through all the rows handling each value individually... I
can't think of any other way to do it except through looping. I'm not sure
what you have against looping, but here is an example for you to consider...

Sub GetRightmostDigit()
Dim X As Long
Dim LastRow As Long
Const SourceColumn As String = "A"
Const DestinationColumn As String = "B"
With Worksheets("Sheet2")
LastRow = .Cells(.Rows.Count, SourceColumn).End(xlUp).Row
For X = 1 To LastRow
If IsNumeric(.Cells(X, SourceColumn).Value) Then
.Cells(X, DestinationColumn).Value = _
Right(.Cells(X, SourceColumn).Value, 1)
End If
Next
End With
End Sub

Rick


"Sheldon Potolsky" wrote in message
...
Rick, I tried what you suggested but preceded it with the line
Columns("A:A").Select
in my VBA code
I then typed in
=--RIGHT(A1)
but that line gave me a compile error so I must be missing something -
Expected: Line number or label or statement or end of statement

I never saw the -- syntax before so perhaps I'm using it incorrectly?

Thanks, Sheldon


=--RIGHT(A1)

If there can be non-numeric data in your columns, then you probably want
this instead...

=IF(ISNUMBER(A1),--RIGHT(A1),"")

Rick

"Sheldon Potolsky" wrote in message

...



Without looping, in VBA, how would I take each numeric value in column
A and change it's value to get only the right-most digit?
Thanks,
Sheldon- Hide quoted text -

- Hide quoted text -

Thanks Rick and Jon. Nothing against looping; I thought it could be
done without looping but I was mistaken so that's the way I'll do it
then. Thanks for the suggestions/code.
Sheldon

On Apr 21, 4:42*pm, "Rick Rothstein \(MVP - VB\)"
wrote:
Actually, that line I gave you was a worksheet formula and not VBA code.
Just put the formula in Row 1 of the column you want the right-most digit in
and copy it down. If you still want to do it via VBA code, then you will
have to loop through all the rows handling each value individually... I
can't think of any other way to do it except through looping. I'm not sure
what you have against looping, but here is an example for you to consider....

Sub GetRightmostDigit()
* Dim X As Long
* Dim LastRow As Long
* Const SourceColumn As String = "A"
* Const DestinationColumn As String = "B"
* With Worksheets("Sheet2")
* * LastRow = .Cells(.Rows.Count, SourceColumn).End(xlUp).Row
* * For X = 1 To LastRow
* * * If IsNumeric(.Cells(X, SourceColumn).Value) Then
* * * * .Cells(X, DestinationColumn).Value = _
* * * * * * * * * * * * * * * *Right(.Cells(X, SourceColumn).Value, 1)
* * * End If
* * Next
* End With
End Sub

Rick

"Sheldon Potolsky" wrote in message

...



Rick, I tried what you suggested but preceded it with the line
Columns("A:A").Select
in my VBA code
I then typed in
=--RIGHT(A1)
but that line gave me a compile error so I must be missing something -
Expected: Line number or label or statement or end of statement

I never saw the -- syntax before so perhaps I'm using it incorrectly?

Thanks, Sheldon

=--RIGHT(A1)

If there can be non-numeric data in your columns, then you probably want
this instead...

=IF(ISNUMBER(A1),--RIGHT(A1),"")

Rick

"Sheldon Potolsky" wrote in message

...

Without looping, in VBA, how would I take each numeric value in column
A and change it's value to get only the right-most digit?
Thanks,
Sheldon- Hide quoted text -

- Hide quoted text -- Hide quoted text -

- Hide quoted text -