VBA to Replace the last number with a 5

[2manytoys]

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:

A_Siteid	AB_Contractor Name	AFF_A_PRCN	AFF_B_RESOURCETYPE
BAR	FLR ENTERPRISES	18891	KLO
BAR	FLR ENTERPRISES	19874	KLO
BAR	FLR ENTERPRISES	19871	KLO
BAR	FLR ENTERPRISES	18895	KLO

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

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.

'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

 

[JoeMo]

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?

 

[2manytoys]

Yes. That is what I want to do.

 

[Nine Zero]

this is assuming your numbers are in column C change the range according to your data

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

 

[JoeMo]

Yes. That is what I want to do.

Here's a non-looping solution that assumes your PRCN header is in C1.

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

 

[Nine Zero]

@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

 

[2manytoys]

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.

 

[JoeMo]

@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).

 

[Nine Zero]

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

 

[JoeMo]

"This" is ambiguous when you have more than one response. Which of the solutions you received are you referring to?