Reply to thread

Message: [QUOTE="Joel, post: 3254001"] This looks like another homework assignment. I only solved some of the problems this code won't work in every situation, but probably will look like it gives the correct answers. You will have to find the cases where it won't work. Sub ExpandRange() Dim StartStr As String Dim EndStr As String Dim StartMSB As String Dim EndMSB As String Dim StartLSB As Double Dim EndLSB As Double 'make header row Range("H2") = "ITEM NAME" Range("I2") = "Item NUMBER" Range("J2") = "DATE" 'format column I as text Columns("I").NumberFormat = "@" NewRow = 3 RowCount = 3 Do While Range("A" & RowCount) <> "" Item = Range("A" & RowCount) StartStr = Range("B" & RowCount) EndStr = Range("C" & RowCount) If StartStr = "" Or _ EndStr = "" Then MsgBox ("Please enter values in Row : " & RowCount & _ vbCrLf & "Exiting Macro") Exit Sub End If 'split Start Number into ' MSB - Most significant part ' LSB - Least significant part If Len(StartStr) > 10 Then StartMSB = Left(StartStr, Len(StartStr) - 10) StartLSB = Val(Right(StartStr, 10)) EndMSB = Left(EndStr, Len(EndStr) - 10) EndLSB = Val(Right(EndStr, 10)) Else StartMSB = "" StartLSB = Val(StartStr) EndMSB = "" EndLSB = Val(EndStr) End If If StartLSB > EndLSB Then MsgBox ("Please provide correct ranges in row : " & RowCount & _ vbCrLf & "Exiting Macro") Exit Sub End If ItemDate = Range("E" & RowCount) 'get number of leading zeroes in LSB 'if numbers If StartMSB <> "" Then If Val(StartMSB) = 0 Then ZeroCount = 0 For CharPos = 1 To Len(StartMSB) If Mid(StartMSB, CharPos, 1) = "0" Then ZeroCount = ZeroCount + 1 Else Exit For End If Next CharPos End If End If If ZeroCount = 0 Then Leader = "" Else Leader = String(ZeroCount, "0") End If I = StartLSB Do While I <= EndLSB Range("H" & NewRow) = Item Range("I" & NewRow) = StartMSB & Leader & I Range("J" & NewRow) = ItemDate NewRow = NewRow + 1 I = I + 1 Loop RowCount = RowCount + 1 Loop End Sub [/QUOTE]

Verification