Next number from a reference list

[kbar]

Okay I am new to macros and this feels like it should be very simple but I need some help. I have a template for creating quotes and a macro that records information from the quote to another sheet including the quote number. I have another macro that then clears certain cells on the quote to prepare for the next one. With this I also want the quote number to update to the next available by referencing the list of already recorded quote numbers from column A on Sheet 3. I can't figure out how to get the code to reference all of column A and return the next number in sequence. Below is the code I am working with but the Range("A:A") part does not work. When I made it a specific cell like A2 then it has no problem returning the next number after what's in A2 but I need it to look through all of column A. Noting that the quote quote number structure is "CH-00001".


Sheet1.Range("I6:M6,A10:D10,B18:H28,K18:K28,F10:M14").ClearContents
Sheet1.Range("I5").Value = Left(Sheet3.Range("A:A").Value, 3) & Mid(Sheet3.Range("A:A").Value, 4, 5) + 1
MsgBox "Your next Change Order number is " & Sheet1.Range("I5")

 

[AlphaFrog]

A simple solution would be to just record the unformatted numeric value in sheet3 column A e.g. instead of recording CH-00001 just record a 1.

Then in sheet1 I5, have the Cell's custom number formatting as "CH-"00000 and the formula =MAX(Sheet3!A:A)+1

You could also format Sheet3 column A with the same custom number format. The actual numeric value in the cell doesn't change with the custom number format. Only how the numeric value is displayed changes.