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:
[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.

'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

 

[2manytoys]

I will try this one. Header is in C2, but I can adjust that.

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

 

[2manytoys]

"This" was to JoeMo. I had not refreshed to see the other replies when I posted the reply. I should have quoted it.

 

[2manytoys]

Both of these work when used as a single code. So far I have not been able to get them to work when copying into the existing code that runs when a button is pressed on the first sheet.

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

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

 

[JoeMo]

We might be able to help you run these from your existing code if you can post it and tell us exactly what it is intended to accomplish, and at what point in it you want to change the PRCN numbers.

 

[2manytoys]

My code probably needs a lot of cleaning up since I have pieced it together from a previous spreadsheet. Monday I will post the entire code for you to look at.

We might be able to help you run these from your existing code if you can post it and tell us exactly what it is intended to accomplish, and at what point in it you want to change the PRCN numbers.