Incremental value with a text prefix and a suffix

[Rowane]

Would appreciate if someone out there could help...I am using the following code to increase my PO number.
My output should look like this - PO09-001/15 where PO09 is fixed and /15 is fixed. However when i run the macro, it turns out PO09-2/15 instead of PO09-002/15. Please help me correct the code. Thank you very much.


Dim i As Integer


Range("C5").Value = "PO09-" & Val(Mid(Range("C5"), 6, Len(Range("C5")) - 5)) + 1 & "/15"

 

[dmt32]

Hi.
As an alternative suggestion why not use a helper cell which would negate need to manipulate cell value in code?

formula in cell C5

="PO09-"&TEXT(C1,"000")&"/15"

in this example I have selected C1 where you would increment PO numbers but it can be any cell.

Hope Helpful

Dave

 

[Rowane]

Hi Dave

Thanks for offering help. Pls pardon me as I am really alien to VBA. My increment PO is on Cell C5. When you suggested the formula, do I add on to the line to the code or?

Hi.
As an alternative suggestion why not use a helper cell which would negate need to manipulate cell value in code?

formula in cell C5

="PO09-"&TEXT(C1,"000")&"/15"

in this example I have selected C1 where you would increment PO numbers but it can be any cell.

Hope Helpful

Dave

 

[Osvaldo Palmeiro]

Hi Rowane.....
Try this

Range("C5").Value = "PO09-" & Format(Val(Mid(Range("C5"), 6, Len(Range("C5")) - 5)) + 1, "000") & "/15"

 

[dmt32]

Hi Dave

Thanks for offering help. Pls pardon me as I am really alien to VBA. My increment PO is on Cell C5. When you suggested the formula, do I add on to the line to the code or?

You place the Formula in C5 & just increment numeric value in your chosen helper cell. No VBA manipulation required.

Dave

 

[Rick Rothstein]

Would appreciate if someone out there could help...I am using the following code to increase my PO number.

Range("C5").Value = "PO09-" & Val(Mid(Range("C5"), 6, Len(Range("C5")) - 5)) + 1 & "/15"

Try that code line like this...

Range("C5").Value = "PO09-" & Format(Mid(Range("C5").Value, 6, 3) + 1, "000") & "/15"