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


"Don Guillett" wrote:

Since Excel only uses the 1st 15, can you limit your number to 15 characters
and then custom format to 00000000000000 15 0's

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Angela" wrote in message
...
Hi,


I want to do a simle routine in excel which will save me alot of time.

I have mentioned the details in the excel file which is at following
location.

http://www.filefactory.com/file/a0ehgfh/n/MakeList_xls

Simply put, I would like to make a list between 2 numbers.
When the first range is done, I want the second range to start from
just below the last cell of first range i.e. no gaps between two
ranges making it a single list. Header of the list is "SAMPLE RANGE"
which I will change later on. Two columns i.e. "ITEM NAME" and "DATE"
also required as mentioned in the sheet.

A small code also required that would count the "QUANITY" in backgound
between "START" and "END" values. Formula would take time as the list
grows.

Some are large numbers, others start with zeros so need to cater that
also.

If there is no start and end, or end value is smaller than start, the
code should give error accordingly.
case1 (when no values) "Please enter values."
case2 (when end is smaller than start) "Ending value is smaller than
Starting value. Please provide correct ranges."


Thx.