indexing using date and an indexing interger cerates mismatch error whwn a leading zero encounted

[IanJG]

I am trying to create an indexing system, that can check and create an index from a number of worksheets and forms as different transactions or events occur, the index number is created by the date formatted "ddmmyyyy" and an indexing integer at the end. example and code below.
everything runs ok until the first of the month (I have custom formated the the index to 9 digits so that the date is always 8 digits long so i can use the left and mid functions.
I think the problem is when the code gets to a index number with a leading 0 it then becomes a mismatch to an integer (i assume intergers can not have leading zeros).
I need to check that an index code has not been used before. there is the ability on another form to manually enter an index number so i need to check against the whole column of index numbers. Any ideas or solutions/work arounds for the, i thing leading zero problem, would be greatly appreatiated . Thanks Ian
PS the point where the problem starts is indicated in red.

<tbody>
[TD="class: xl65, align: right"]271020140[/TD]

[TD="class: xl65, align: right"]271020141[/TD]

[TD="class: xl65, align: right"] 011120141 [/TD]

[TD="class: xl65, align: right"]011120142[/TD]

</tbody>

TheCode :-

Sub indextest()

Dim RW As Integer
Dim DS As String
Dim TD As String
Dim SL As Integer
Dim indx As Integer
Dim xL As Integer
Dim Nindex As Integer
Dim Cindex As String
Dim RN As Integer
Dim CNT As Integer


TD = Format(Date, "ddmmyyyy") ' todays date
MsgBox ("todays date is " & TD)
RW = ThisWorkbook.Sheets("Log").Range("a" & Rows.Count).End(xlUp).Row ' number of rows with data

CNT = 0


'MsgBox ThisWorkbook.Sheets("Log").Range("a" & Rows.Count).End(xlUp).Row
'SL = Len(ThisWorkbook.Sheets("Log").Range("a" & Rows.Count).End(xlUp))
'xL = SL - 8


For RN = 2 To RW
DS = Left(ThisWorkbook.Sheets("Log").Range("a" & RN), 8) ' extract date from cell
SL = Len(ThisWorkbook.Sheets("Log").Range("a" & RN)) ' calculates number of digits in index
xL = SL - 8 ' calculates the digits in the indeing number

MsgBox ("date in cell" & DS & " " & "sl = " & SL & " " & "xL = " & xL)

Cindex = Mid(ThisWorkbook.Sheets("Log").Range("a" & RN), 9, xL)
MsgBox ("Cindex = " & Cindex)


End If
Next



End Sub



[TABLE="width: 138"]
<tbody>[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[RickXL]

Hi,

I think you might need to format the cells as Text. That will stop the leading zeros disappearing.

Possibly a better way, use the dates the other way round - YYYYMMDD. That will remove the leading zero problem. Also, the numbers, if sorted, will be in date order. The flow of most significant digit to least significant will then be logical from left to right including the index.

Other options, Add a single quote to the values in the cells. I find that works until you try a VLOOKUP then it takes you ages to find out why things can't be looked up. Add another character (e.g. D for Date) to the start then there will be no leading zero.

In my opinion, leading zeros always cause problems. So I always try and design things so they can't ever happen.

 

[IanJG]

thanks Rick
reversing the order sounds like a good simple solution


thanks ian

 

[RickXL]

No problem.

Thanks for the feedback.