I am using the following code to insert missing rows of alphanumeric values. While it does work it is removing the leading zeros as shown below...
I have tried playing the the cell formats, with no sucess, Any ideas how to correct this behavior???

Also how can I add the ability to verify the number sequence will start at LxxD159 & LxxM159 respectively, where xx ranges from 1 to 10 (this could get the value from another worksheet.)



L01D001
L01D002
L01D003
L01D159
L01M001
L01M002
L01M3 <----
L01M4 <----
L01M005


Sub test()
Dim val1 As String, txt1 As String, xNum As Long
Dim WorkRows As Long, _
Ndx As Long, _
Diff As Long, _
InsertCounter As Integer, _
WorkColumn As String

WorkColumn = "A" ' <<<<<<< CHANGE TO YOUR COLUMN

WorkRows = Cells(Rows.Count, WorkColumn).End(xlUp).Row

'Starting Len Value
xNum = 5

'Start at the bottom of the list and work up to the top
'that way ndx will always poin to the row just above the ones
'that were inserted

For Ndx = WorkRows To 2 Step -1
Cells(Ndx, WorkColumn).Activate

val1 = Selection.Cells(1).Value
txt1 = Left(val1, xNum - 1)

'establish the rows to insert
val1 = Right(Selection.Cells(1).Value, Len(Selection.Cells(1).Value) - xNum + 1)

Diff = Right(Cells(Ndx, WorkColumn).Value, Len(Cells(Ndx, WorkColumn).Value) - xNum + 1) - Right(Cells(Ndx - 1, WorkColumn).Value, Len(Cells(Ndx - 1, WorkColumn).Value) - xNum + 1)

If Diff 1 Then
For InsertCounter = 1 To Diff - 1
Range(WorkColumn & Ndx).EntireRow.Insert

ActiveCell.Value = txt1 & Right(ActiveCell.Offset(1, 0).Value, Len(ActiveCell.Offset(1, 0).Value) - xNum + 1) - 1

Next InsertCounter
End If
Next Ndx
End Sub

On Mon, 4 Feb 2013 23:37:14 +0000, TimLeonard wrote:

ActiveCell.Value = txt1 & Right(ActiveCell.Offset(1,0).Value, Len(ActiveCell.Offset(1, 0).Value) - xNum + 1) - 1

When you do the subtraction operation in the above line, the result is an unformatted number for that portion of the string. If you need it to always be padded to three digits, you need it to return a string.
e.g:

ActiveCell.Value = txt1 & Format(Right(ActiveCell.Offset(1, 0).Value, _
Len(ActiveCell.Offset(1, 0).Value) - xNum + 1) - 1, "000")

On Mon, 4 Feb 2013 23:37:14 +0000, TimLeonard wrote:

Also how can I add the ability to verify the number sequence will start
at LxxD159 & LxxM159 respectively, where xx ranges from 1 to 10 (this
could get the value from another worksheet.)


In your example, you show the sequence ending at those values, not starting. Please clarify.

Also, I expect your sheet is more complex than what you show. But if all you want to do is add the missing rows, it would likely be much faster to add the missing rows at the bottom, and then sort the results. Exactly how that could best be done would depend on what your sheet really looks like.

First off thank you very much for the reply, I knew it was something simple but I just couldn't find it...

I was referring to how the script functions...It starts at the bottom of the column and looks for the missing values and then adds it.

The intended function...
Ultimately the intent is to compare a database on two separate worksheets. One database is manually updated or modified and the other is exported from a field panel and only contains what is programmed in the panel. That’s why this script is adding the missing row values to the field database so that the two worksheets will match, then a comparison can be made between the worksheets and display the differences on a third worksheet.

For the ability to verify the number sequence will start at LxxD159 & LxxM159 respectively, where xx ranges from 1 to 10....
The panel supports the capacity of 159 D's and 159 M's on up to 10 different loops. The manually updated worksheet already has the values ranging from 1 to 159. Therefore I was looking for a way to verify in the panel worksheet the quantity of loops and then add the missing values starting at 159 and moving backwards through the columns so the worksheet rows will match for the comparison. BTW, the quantity of loops can coms from a column on the panel worksheet

I hope this is understandable...

On Tue, 5 Feb 2013 17:55:33 +0000, TimLeonard wrote:


First off thank you very much for the reply, I knew it was something
simple but I just couldn't find it...



Glad to help. Thanks for the feedback.


In your example, you show the sequence ending at those values, not
starting. Please clarify.

I was referring to how the script functions...It starts at the bottom of
the column and looks for the missing values and then adds it.

OK


The intended function...
Ultimately the intent is to compare a database on two separate
worksheets. One database is manually updated or modified and the other
is exported from a field panel and only contains what is programmed in
the panel. That’s why this script is adding the missing row
values to the field database so that the two worksheets will match, then
a comparison can be made between the worksheets and display the
differences on a third worksheet.

For the ability to verify the number sequence will start at LxxD159 &
LxxM159 respectively, where xx ranges from 1 to 10....
The panel supports the capacity of 159 D's and 159 M's on up to 10
different loops. The manually updated worksheet already has the values
ranging from 1 to 159. Therefore I was looking for a way to verify in
the panel worksheet the quantity of loops and then add the missing
values starting at 159 and moving backwards through the columns so the
worksheet rows will match for the comparison. BTW, the quantity of
loops can coms from a column on the panel worksheet

I hope this is understandable...

I think you might be making things needlessly complex. If you are looking for the differences between the two sheets, there are probably simpler ways to do the comparison, depending on how you want the differences reported.

Perhaps a sample workbook would explain things better.....
I have made worksheets "Summary" and "CompareDate" to reflect the end results which will then display the difference on the "Diff" sheet...

Note that the "Summary" sheet is manually updated based on when device addresses is added to a drawing and the "comparedata" is when the device addresses is programmed in the field panel...

Remember I am trying to make script to add the missing number sequence so the sample sheet "Orig Panel Data" would look like the "CompareDate" sheet to make the comparision work...

I am open to suggestions as to wasys to make this simpler and or faster

Thanks again

On Thu, 7 Feb 2013 04:29:27 +0000, TimLeonard wrote:

I am open to suggestions as to wasys to make this simpler and or faster

A few questions after reviewing and some preliminaries:
1. If I remove lines that do not have any entry for "Device Types" form the Summary and CompareData sheets, will the result be those sheets without the extra rows?
2. Do you need to compare every cell in every row? If so, how do you account for the fact that Summary has 15 columns and CompareData only has 14 columns (missing column A).