Overflow (Error 6) for Incremental Value

[Rowane]

Hi all

I am a newbie in this. I have copied a code to increase my delivery order value in Cell C5 which time i click a control button. Code is as follows:-

Dim num As Integer
Range("C5").Select
num = Range("C5").Value
num = num + 1
Range("C5").Value = num


End Sub

If the number of the C5 is 5 digits, no problem for the value to increase. But my DO number is eg. 1500983, I encountered "Overflow (Error 6)" message and when i clicked on "Debug", "num = Range("C5").Value" is highlighted.

I have no idea how to solve this issue. Would greatly appreciate if anyone could help me with this error.

 

[Trebor76]

Hi Rowane,

Welcome to MrExcel!!

An integer variable can 'only' hold whole numbers in the range -32,768 to 32,767. Changing the variable Integer to Long (which can hold numbers from -2,147,483,648 to 2,147,483,647) will solve the problem.

Regards,

Robert

 

[Rowane]

Thanks Robert...pardon me....what is the code i should amend? I am really alien to Macro....

 

[Rowane]

Hi Robert, I have figured it out...thanks a million!

 

[Rowane]

Hi can i post another question on adding a prefix to the delivery number? eg. DO 1500983
so each time i click on the control button, it will become DO 1500984?

appreciate if you write me the code. Thanks!

 

[Trebor76]

Hi Rowane,

Generally you should start a new thread for each new question but as it's somewhat related I suppose it's OK.

Putting text in front of a number no longer makes that a number so incrementing it will become slightly problematic. Is the format always going to be 'DO' followed by a space then seven digits?

Robert

 

[Rowane]

Yes...always start with "DO" followed by a space and then six digits. not seven..sorry..

 

[Trebor76]

...and is there a space before the numbers start (as your thread 5 suggests) like 'DO 1500984' or not so it's like 'DO1500984'. I need to know as it makes a difference for the proposed solution.

 

[Rowane]

Hi Robert

Apologies for the confusion. To be precise, the output should be DO 150119.
ie. "DO" with a space, followed with by six digits.

 

[Trebor76]

As long as there's an entry in cell C5 such as DO 150119, this will do the job:

Range("C5").Value = "DO " & Val(Mid(Range("C5"), 4, Len(Range("C5")) - 3)) + 1