VBA to Replace the last number with a 5

2manytoys

New Member
Joined
Jul 27, 2018
Messages
7
I have a report that I am working on. In the report one of the columns contains a number and I want to evaluate the last number and if it is not "5", I want to change it to "5".

Some Data:
[TABLE="width: 438"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]A_Siteid[/TD]
[TD]AB_Contractor Name[/TD]
[TD]AFF_A_PRCN[/TD]
[TD]AFF_B_RESOURCETYPE[/TD]
[/TR]
[TR]
[TD]BAR[/TD]
[TD]FLR ENTERPRISES[/TD]
[TD="align: center"]18891[/TD]
[TD]KLO[/TD]
[/TR]
[TR]
[TD]BAR[/TD]
[TD]FLR ENTERPRISES[/TD]
[TD="align: center"]19874[/TD]
[TD]KLO[/TD]
[/TR]
[TR]
[TD]BAR[/TD]
[TD]FLR ENTERPRISES[/TD]
[TD="align: center"]19871[/TD]
[TD]KLO[/TD]
[/TR]
[TR]
[TD]BAR[/TD]
[TD]FLR ENTERPRISES[/TD]
[TD="align: center"]18895[/TD]
[TD]KLO
[/TD]
[/TR]
</tbody>[/TABLE]

Most of my VBA for this spreadsheet is a loop that looks for values and makes the changes needed. The one to replace the Resource Type works fine, but I can't figure out how o look for the last digit in the PRCN.
Code:
'Change Resource Type to "KLO".
rowa = 3
    Do Until Sheet11.Cells(rowa, 2).Value = Empty
         If Sheet11.Cells(rowa, 4).Value <> "KLO" Then
            Sheet11.Cells(rowa, 4).Value = "KLO"
             End If
rowa = rowa + 1
  Loop
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Do you mean you want to look at each PRCN number and if the last digit in that number is not a 5 you want to change it to 5 leaving the rest of the PRCN intact?
 
Upvote 0
this is assuming your numbers are in column C change the range according to your data

Code:
Sub Five()
Dim r As Range
    For Each r In Range("C1:C10") 'change this to the range of your numbers
        If r.Value > 0 And Not Right(r.Value, 1) = 5 Then
            r.Value = Left(r.Value, Len(r.Value) - 1) & 5
        End If
    Next r
End Sub
 
Upvote 0
Yes. That is what I want to do.
Here's a non-looping solution that assumes your PRCN header is in C1.
Code:
Sub LastPRCN_DigitIs5()
Application.ScreenUpdating = False
With Range("C2:C" & Cells(Rows.Count, "C").End(xlUp).Row)
    .Value = Evaluate(Replace("If(@="""","""",(Left(@, 4) & ""5"") + 0)", "@", .Address(0, 0)))
End With
Application.ScreenUpdating = True
End Sub
 
Upvote 0
@JoeMo

Whoa I had no idea I could use WITH with a RANGE.... that makes tons of loops ive created obsolete .... learn something new everyday ... thank you
 
Upvote 0
This works if I run it by itself with the sheet I want to change opened. I'm trying to incorporate this into the code I already have for the sheet that does multiple other things. I think I can get it pointed to the correct sheet but my Range will not be the same each time I run the report. It could be 45 rows or it could be 200 rows depending on how many submittals I have for the month.
 
Upvote 0
@JoeMo

Whoa I had no idea I could use WITH with a RANGE.... that makes tons of loops ive created obsolete .... learn something new everyday ... thank you
It's a clever way to avoid loops that's traceable to Rick Rothstein (I think).
 
Last edited:
Upvote 0
Im sure you can place t to work on the sheet of your choice, as far as changing row amount... use Joe's answer as his takes the amount of rows being used into account
 
Upvote 0
"This" is ambiguous when you have more than one response. Which of the solutions you received are you referring to?
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,906
Members
452,366
Latest member
TePunaBloke

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