Updating claim number - WorksheetFunction?

[MJPeters]

Long time lurker - first time post. I've been trying to figure this out on and off for a couple weeks. It took a bit for me to come crawling out of the shadows. I import claim data on a daily basis at work and one process I still can't figure out how to automate.

Range("J14:M14") is named "ClaimNumber" and can contain one of two things depending on if it's an initial or subsequent claim.

Subsequent - A full claim number (something like 07236-019-152-001 or 07226-009-148-023)
Initial - A claim number ending in xxx (07236-019-152-xxx, 07226-009-148-xxx)

All I need to do is update xxx to 001 if it's an initial claim or update the last three digits to count up one (001 -> 002, 023 -> 024)

I've played around with Application.WorksheetFunction but keep running into various errors and I understand how to accomplish part of this with excel functions outside of VBA. I'll need to use an if statement to change xxx to 001 which shouldn't be that difficult.

Left(ClaimNumber, 14) & Text(Right(ClaimNumber, 3) + 1, "000")

What direction should I head to accomplish this in VBA?

Thanks for looking and hello world!
-Mitchell

 

[elmer007]

I think this might get you started; it works if the value (07236-019-152-023 or 07236-019-152-xxx) is in cell A1, and it puts the result in cell A2:

Dim myRange As Range
Set myRange = Sheets("Sheet1").Range("A1")

If Right(myRange, 3) = "xxx" Then
Sheets("Sheet1").Range("A2").Value = Left(myRange, 14) & "001"
Else
Sheets("Sheet1").Range("A2").Value = Left(myRange, 14) & Format((Right(myRange, 3) + 1), "000")
End If

So your code would be something like:

If Right(Range("ClaimNumber"), 3) = "xxx" Then
Sheets("Sheet1").Range("A2").Value = Left(Range("ClaimNumber"), 14) & "001"
Else
Sheets("Sheet1").Range("A2").Value = Left(Range("ClaimNumber"), 14) & Format((Right(Range("ClaimNumber"), 3) + 1), "000")
End If

 

[MJPeters]

That got me exactly where I needed! Thank you, elmer007. I'll be able to expand on that idea. :D

Please let me know if I should be giving you some kind of acknowledgement/cred. I did skim through the forum FAQs and etiquette but I may have missed something.

 

[elmer007]

No worries, glad to help