View Full Version : Loop formula to remove last letter of productcode if it is a letter
Riaaz66
09-24-2010, 06:15 AM
Hi Guys,
In column A, I have a list of productcodes. Some of them end with a letter.
I need to have a loop formula where the last character of the productcode should be removed ONLY if it ends with a letter.
I do not know how to determine that the last character is a letter.
Productcode Change To
TRS11580 TRS11580
TRS8180B TRS8180
TRS11155G TRS11155
TRS11301C TRS11301
TRS9029C TRS9029
TRS11749 TRS11749
TRS11282 TRS11282
SWAP2589A SWAP2589
TRS10079 TRS10079
TRS2579B TRS2579B
Thanks in advance,
Riaaz66
Bob Phillips
09-24-2010, 08:13 AM
Public Sub ProcessData()
Dim Lastrow As Long
Dim i As Long
Application.ScreenUpdating = False
With ActiveSheet
Lastrow = .Cells(.Rows.Count, "a").End(xlUp).Row
For i = 2 To Lastrow
If Not IsNumeric(Right$(.Cells(i, "A").Value2, 1)) Then
.Cells(i, "A").Value2 = Left$(.Cells(i, "A").Value2, Len(.Cells(i, "A").Value2) - 1)
End If
Next i
End With
Application.ScreenUpdating = True
End Sub
Riaaz66
09-27-2010, 01:30 AM
Hi Xld,
Thank you very much. My problem is solved.
This thread can be marked as closed.
Thanks again.
Riaaz66
09-27-2010, 02:16 AM
Oops,
I think I was to enthousiastic in my previous post. Really sorry for that.
My data starts in column B6 up to the end. If I run the provided macro, I get this error:
Run-time error '5':Invalid procedure call or argument. When I press the debug button, the following line is marked yellow:
.Cells(i, "A").Value2 = Left$(.Cells(i, "A").Value2, Len(.Cells(i, "A").Value2) - 1)
However, when I copy the data on column A1, the macro runs fine.
I want the macro to be able to run from a activecell till the last row of that column.
Can you please also expalin we the "a" and the "A" in the code. Does it refer to a letter or to a column?
Thanks in advance and kind regards,
Riaaz66
macropod
09-27-2010, 02:24 AM
FWIW, a formula can strip off the letter and output the result in another cell. For example:
=IF(CODE(RIGHT(A2))>64,LEFT(A2,LEN(A2)-1),A2)
Bob Phillips
09-27-2010, 02:57 AM
Oops,
I think I was to enthousiastic in my previous post. Really sorry for that.
My data starts in column B6 up to the end. If I run the provided macro, I get this error:
Run-time error '5':Invalid procedure call or argument. When I press the debug button, the following line is marked yellow:
.Cells(i, "A").Value2 = Left$(.Cells(i, "A").Value2, Len(.Cells(i, "A").Value2) - 1)
However, when I copy the data on column A1, the macro runs fine.
I want the macro to be able to run from a activecell till the last row of that column.
Can you please also expalin we the "a" and the "A" in the code. Does it refer to a letter or to a column?
Thanks in advance and kind regards,
Riaaz66
Just change it to
Public Sub ProcessData()
Dim Lastrow As Long
Dim i As Long
Application.ScreenUpdating = False
With ActiveSheet
Lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = 6 To Lastrow
If Not IsNumeric(Right$(.Cells(i, "B").Value2, 1)) Then
.Cells(i, "B").Value2 = Left$(.Cells(i, "B").Value2, Len(.Cells(i, "B").Value2) - 1)
End If
Next i
End With
Application.ScreenUpdating = True
End Sub
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.