Using RIGHT function to get right-most digit for each cell in acolumn

[Sheldon Potolsky]

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 Rothstein \(MVP - VB\)]

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]

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

 

[Jon Peltier]

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

 

[Rick Rothstein \(MVP - VB\)]

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]

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