Insert rows
 

I have 2 columns of data, A2:A? contains week numbers in ascending order and
B2:B? contains orders received in that week.

I now have to put this date into a chart. This is no problem but there are
some week numbers missing, want I want is a little macro to add in the
missing numbers and enter a 0 in colun B . For example:

Column A Column B
Week No. Orders
1 23
2 29
5 23
6 56
8 55


This should look like:

Column A Column B
Week No. Orders
1 23
2 29
3 0
4 0
5 23
6 56
7 0
8 55

Thaks in advance.

Gareth

Insert rows
 

Gareth

one way:

Sub InsertRows()
Dim LastRow As Long
Dim i As Long
Dim InsertCount As Long
Application.ScreenUpdating = False
InsertCount = 1
Do Until InsertCount = 0
InsertCount = 0
LastRow = Range("A65536").End(xlUp).Row
For i = LastRow To 3 Step -1
If Range("A" & i).Value < Range("A" & i - 1).Value + 1 Then
Range("A" & i).EntireRow.Insert
Range("A" & i) = Range("A" & i).Offset(1, 0).Value - 1
Range("A" & i).Offset(0, 1) = 0
InsertCount = 1
End If
Next 'i
Loop
Application.ScreenUpdating = True
End Sub

Regards

Trevor


"Gareth" wrote in message
...
I have 2 columns of data, A2:A? contains week numbers in ascending order
and
B2:B? contains orders received in that week.

I now have to put this date into a chart. This is no problem but there
are
some week numbers missing, want I want is a little macro to add in the
missing numbers and enter a 0 in colun B . For example:

Column A Column B
Week No. Orders
1 23
2 29
5 23
6 56
8 55


This should look like:

Column A Column B
Week No. Orders
1 23
2 29
3 0
4 0
5 23
6 56
7 0
8 55

Thaks in advance.

Gareth

Insert rows
 

Trevor

I cannot seem to get your code to work at all, just to recap:

I want a macro to insert all numbers in ascending order in column A, given
that certain numbers may be missing. Against these added numbes I would
like a 0 entered into column B.

There may be 3, 4, 5, 6, 7, 8 ..... numbers missing.

Gareth

"Trevor Shuttleworth" wrote in message
...
Gareth

one way:

Sub InsertRows()
Dim LastRow As Long
Dim i As Long
Dim InsertCount As Long
Application.ScreenUpdating = False
InsertCount = 1
Do Until InsertCount = 0
InsertCount = 0
LastRow = Range("A65536").End(xlUp).Row
For i = LastRow To 3 Step -1
If Range("A" & i).Value < Range("A" & i - 1).Value + 1 Then
Range("A" & i).EntireRow.Insert
Range("A" & i) = Range("A" & i).Offset(1, 0).Value - 1
Range("A" & i).Offset(0, 1) = 0
InsertCount = 1
End If
Next 'i
Loop
Application.ScreenUpdating = True
End Sub

Regards

Trevor


"Gareth" wrote in message
...
I have 2 columns of data, A2:A? contains week numbers in ascending order
and
B2:B? contains orders received in that week.

I now have to put this date into a chart. This is no problem but there
are
some week numbers missing, want I want is a little macro to add in the
missing numbers and enter a 0 in colun B . For example:

Column A Column B
Week No. Orders
1 23
2 29
5 23
6 56
8 55


This should look like:

Column A Column B
Week No. Orders
1 23
2 29
3 0
4 0
5 23
6 56
7 0
8 55

Thaks in advance.

Gareth

Insert rows
 

Gareth

works for me. Tested up to 10 numbers missing. Fills the gaps and adds a 0
in column B.

What doesn't work for you ? The only thought is that the data in column A
is not being seen as numeric. If I put a single quote in front of the
numbers they then appear left aligned but that really screws up the
calculations. The code then appears to loop but it is actually working and
adding lots of rows of data.

Try it with an additional test for numeric data:

Sub InsertRows()
Dim LastRow As Long
Dim i As Long
Dim InsertCount As Long
Application.ScreenUpdating = False
InsertCount = 1
Do Until InsertCount = 0
InsertCount = 0
LastRow = Range("A65536").End(xlUp).Row
For i = LastRow To 3 Step -1
If Not Application.WorksheetFunction.IsNumber(Range("A" & i).Value)
Then
MsgBox "Error in data"
Exit Sub
End If
If Range("A" & i).Value < Range("A" & i - 1).Value + 1 Then
Range("A" & i).EntireRow.Insert
Range("A" & i) = Range("A" & i).Offset(1, 0).Value - 1
Range("A" & i).Offset(0, 1) = 0
InsertCount = 1
End If
Next 'i
Loop
Application.ScreenUpdating = True
End Sub


Regards

Trevor


"Gareth" wrote in message
...
Trevor

I cannot seem to get your code to work at all, just to recap:

I want a macro to insert all numbers in ascending order in column A, given
that certain numbers may be missing. Against these added numbes I would
like a 0 entered into column B.

There may be 3, 4, 5, 6, 7, 8 ..... numbers missing.

Gareth

"Trevor Shuttleworth" wrote in message
...
Gareth

one way:

Sub InsertRows()
Dim LastRow As Long
Dim i As Long
Dim InsertCount As Long
Application.ScreenUpdating = False
InsertCount = 1
Do Until InsertCount = 0
InsertCount = 0
LastRow = Range("A65536").End(xlUp).Row
For i = LastRow To 3 Step -1
If Range("A" & i).Value < Range("A" & i - 1).Value + 1 Then
Range("A" & i).EntireRow.Insert
Range("A" & i) = Range("A" & i).Offset(1, 0).Value - 1
Range("A" & i).Offset(0, 1) = 0
InsertCount = 1
End If
Next 'i
Loop
Application.ScreenUpdating = True
End Sub

Regards

Trevor


"Gareth" wrote in message
...
I have 2 columns of data, A2:A? contains week numbers in ascending
order
and
B2:B? contains orders received in that week.

I now have to put this date into a chart. This is no problem but
there
are
some week numbers missing, want I want is a little macro to add in the
missing numbers and enter a 0 in colun B . For example:

Column A Column B
Week No. Orders
1 23
2 29
5 23
6 56
8 55


This should look like:

Column A Column B
Week No. Orders
1 23
2 29
3 0
4 0
5 23
6 56
7 0
8 55

Thaks in advance.

Gareth

Insert rows
 

I generated 40 unique numbers between 1 and 1000, then sorted them. Put the
number 1 in cell A2, the remainder of the numbers were in 3 to 42 (highest
number was 851).

Ran the first macro posted by Trevor and it worked fine.

--
Regards,
Tom Ogilvy


Trevor Shuttleworth wrote in message
...
Gareth

works for me. Tested up to 10 numbers missing. Fills the gaps and adds a
0
in column B.

What doesn't work for you ? The only thought is that the data in column A
is not being seen as numeric. If I put a single quote in front of the
numbers they then appear left aligned but that really screws up the
calculations. The code then appears to loop but it is actually working
and
adding lots of rows of data.

Try it with an additional test for numeric data:

Sub InsertRows()
Dim LastRow As Long
Dim i As Long
Dim InsertCount As Long
Application.ScreenUpdating = False
InsertCount = 1
Do Until InsertCount = 0
InsertCount = 0
LastRow = Range("A65536").End(xlUp).Row
For i = LastRow To 3 Step -1
If Not Application.WorksheetFunction.IsNumber(Range("A" &
i).Value)
Then
MsgBox "Error in data"
Exit Sub
End If
If Range("A" & i).Value < Range("A" & i - 1).Value + 1 Then
Range("A" & i).EntireRow.Insert
Range("A" & i) = Range("A" & i).Offset(1, 0).Value - 1
Range("A" & i).Offset(0, 1) = 0
InsertCount = 1
End If
Next 'i
Loop
Application.ScreenUpdating = True
End Sub


Regards

Trevor


"Gareth" wrote in message
...
Trevor

I cannot seem to get your code to work at all, just to recap:

I want a macro to insert all numbers in ascending order in column A,
given
that certain numbers may be missing. Against these added numbes I would
like a 0 entered into column B.

There may be 3, 4, 5, 6, 7, 8 ..... numbers missing.

Gareth

"Trevor Shuttleworth" wrote in message
...
Gareth

one way:

Sub InsertRows()
Dim LastRow As Long
Dim i As Long
Dim InsertCount As Long
Application.ScreenUpdating = False
InsertCount = 1
Do Until InsertCount = 0
InsertCount = 0
LastRow = Range("A65536").End(xlUp).Row
For i = LastRow To 3 Step -1
If Range("A" & i).Value < Range("A" & i - 1).Value + 1 Then
Range("A" & i).EntireRow.Insert
Range("A" & i) = Range("A" & i).Offset(1, 0).Value - 1
Range("A" & i).Offset(0, 1) = 0
InsertCount = 1
End If
Next 'i
Loop
Application.ScreenUpdating = True
End Sub

Regards

Trevor


"Gareth" wrote in message
...
I have 2 columns of data, A2:A? contains week numbers in ascending
order
and
B2:B? contains orders received in that week.

I now have to put this date into a chart. This is no problem but
there
are
some week numbers missing, want I want is a little macro to add in
the
missing numbers and enter a 0 in colun B . For example:

Column A Column B
Week No. Orders
1 23
2 29
5 23
6 56
8 55


This should look like:

Column A Column B
Week No. Orders
1 23
2 29
3 0
4 0
5 23
6 56
7 0
8 55

Thaks in advance.

Gareth

Insert rows
 

Tom and Trevor

I now know what the problem is. The data in column A is
imported from an Access query and is not seen by Excel as
a number.

The data is made up of week numbers calculated thus:

=Format([Orderdate],"ww")

Is there a way to 'make' it a number, either in Access or
in Excel before running the macro?

Gareth

-----Original Message-----
I generated 40 unique numbers between 1 and 1000, then
sorted them. Put the
number 1 in cell A2, the remainder of the numbers were in
3 to 42 (highest
number was 851).

Ran the first macro posted by Trevor and it worked fine.

--
Regards,
Tom Ogilvy


Trevor Shuttleworth wrote in
message
...
Gareth

works for me. Tested up to 10 numbers missing. Fills
the gaps and adds a
0
in column B.

What doesn't work for you ? The only thought is that
the data in column A
is not being seen as numeric. If I put a single quote
in front of the
numbers they then appear left aligned but that really
screws up the
calculations. The code then appears to loop but it is
actually working
and
adding lots of rows of data.

Try it with an additional test for numeric data:

Sub InsertRows()
Dim LastRow As Long
Dim i As Long
Dim InsertCount As Long
Application.ScreenUpdating = False
InsertCount = 1
Do Until InsertCount = 0
InsertCount = 0
LastRow = Range("A65536").End(xlUp).Row
For i = LastRow To 3 Step -1
If Not Application.WorksheetFunction.IsNumber
(Range("A" &
i).Value)
Then
MsgBox "Error in data"
Exit Sub
End If
If Range("A" & i).Value < Range("A" & i -
1).Value + 1 Then
Range("A" & i).EntireRow.Insert
Range("A" & i) = Range("A" & i).Offset(1,
0).Value - 1
Range("A" & i).Offset(0, 1) = 0
InsertCount = 1
End If
Next 'i
Loop
Application.ScreenUpdating = True
End Sub


Regards

Trevor


"Gareth" wrote in
message
...
Trevor

I cannot seem to get your code to work at all, just
to recap:

I want a macro to insert all numbers in ascending
order in column A,
given
that certain numbers may be missing. Against these
added numbes I would
like a 0 entered into column B.

There may be 3, 4, 5, 6, 7, 8 ..... numbers missing.

Gareth

"Trevor Shuttleworth"
wrote in message
...
Gareth

one way:

Sub InsertRows()
Dim LastRow As Long
Dim i As Long
Dim InsertCount As Long
Application.ScreenUpdating = False
InsertCount = 1
Do Until InsertCount = 0
InsertCount = 0
LastRow = Range("A65536").End(xlUp).Row
For i = LastRow To 3 Step -1
If Range("A" & i).Value < Range("A" & i -
1).Value + 1 Then
Range("A" & i).EntireRow.Insert
Range("A" & i) = Range("A" & i).Offset
(1, 0).Value - 1
Range("A" & i).Offset(0, 1) = 0
InsertCount = 1
End If
Next 'i
Loop
Application.ScreenUpdating = True
End Sub

Regards

Trevor


"Gareth" wrote in
message
...
I have 2 columns of data, A2:A? contains week
numbers in ascending
order
and
B2:B? contains orders received in that week.

I now have to put this date into a chart. This
is no problem but
there
are
some week numbers missing, want I want is a
little macro to add in
the
missing numbers and enter a 0 in colun B . For
example:

Column A Column B
Week No. Orders
1 23
2 29
5 23
6 56
8 55


This should look like:

Column A Column B
Week No. Orders
1 23
2 29
3 0
4 0
5 23
6 56
7 0
8 55

Thaks in advance.

Gareth











.

Insert rows
 

Gareth

Sub InsertRows()
Dim LastRow As Long
Dim i As Long
Dim InsertCount As Long
Application.ScreenUpdating = False
' Convert data to numbers from "text"
LastRow = Range("A65536").End(xlUp).Row
For i = 2 To LastRow
Range("A" & i).Value = Range("A" & i).Value
Next 'i
' Insert new lines with zero values
InsertCount = 1
Do Until InsertCount = 0
InsertCount = 0
LastRow = Range("A65536").End(xlUp).Row
For i = LastRow To 3 Step -1
If Not Application.WorksheetFunction.IsNumber(Range("A" & i).Value)
Then
MsgBox "Error in data"
Exit Sub
End If
If Range("A" & i).Value < Range("A" & i - 1).Value + 1 Then
Range("A" & i).EntireRow.Insert
Range("A" & i) = Range("A" & i).Offset(1, 0).Value - 1
Range("A" & i).Offset(0, 1) = 0
InsertCount = 1
End If
Next 'i
Loop
Application.ScreenUpdating = True
End Sub

Regards

Trevor


"Gareth" wrote in message
...
Tom and Trevor

I now know what the problem is. The data in column A is
imported from an Access query and is not seen by Excel as
a number.

The data is made up of week numbers calculated thus:

=Format([Orderdate],"ww")

Is there a way to 'make' it a number, either in Access or
in Excel before running the macro?

Gareth

-----Original Message-----
I generated 40 unique numbers between 1 and 1000, then
sorted them. Put the
number 1 in cell A2, the remainder of the numbers were in
3 to 42 (highest
number was 851).

Ran the first macro posted by Trevor and it worked fine.

--
Regards,
Tom Ogilvy


Trevor Shuttleworth wrote in
message
...
Gareth

works for me. Tested up to 10 numbers missing. Fills
the gaps and adds a
0
in column B.

What doesn't work for you ? The only thought is that
the data in column A
is not being seen as numeric. If I put a single quote
in front of the
numbers they then appear left aligned but that really
screws up the
calculations. The code then appears to loop but it is
actually working
and
adding lots of rows of data.

Try it with an additional test for numeric data:

Sub InsertRows()
Dim LastRow As Long
Dim i As Long
Dim InsertCount As Long
Application.ScreenUpdating = False
InsertCount = 1
Do Until InsertCount = 0
InsertCount = 0
LastRow = Range("A65536").End(xlUp).Row
For i = LastRow To 3 Step -1
If Not Application.WorksheetFunction.IsNumber
(Range("A" &
i).Value)
Then
MsgBox "Error in data"
Exit Sub
End If
If Range("A" & i).Value < Range("A" & i -
1).Value + 1 Then
Range("A" & i).EntireRow.Insert
Range("A" & i) = Range("A" & i).Offset(1,
0).Value - 1
Range("A" & i).Offset(0, 1) = 0
InsertCount = 1
End If
Next 'i
Loop
Application.ScreenUpdating = True
End Sub


Regards

Trevor


"Gareth" wrote in
message
...
Trevor

I cannot seem to get your code to work at all, just
to recap:

I want a macro to insert all numbers in ascending
order in column A,
given
that certain numbers may be missing. Against these
added numbes I would
like a 0 entered into column B.

There may be 3, 4, 5, 6, 7, 8 ..... numbers missing.

Gareth

"Trevor Shuttleworth"
wrote in message
...
Gareth

one way:

Sub InsertRows()
Dim LastRow As Long
Dim i As Long
Dim InsertCount As Long
Application.ScreenUpdating = False
InsertCount = 1
Do Until InsertCount = 0
InsertCount = 0
LastRow = Range("A65536").End(xlUp).Row
For i = LastRow To 3 Step -1
If Range("A" & i).Value < Range("A" & i -
1).Value + 1 Then
Range("A" & i).EntireRow.Insert
Range("A" & i) = Range("A" & i).Offset
(1, 0).Value - 1
Range("A" & i).Offset(0, 1) = 0
InsertCount = 1
End If
Next 'i
Loop
Application.ScreenUpdating = True
End Sub

Regards

Trevor


"Gareth" wrote in
message
...
I have 2 columns of data, A2:A? contains week
numbers in ascending
order
and
B2:B? contains orders received in that week.

I now have to put this date into a chart. This
is no problem but
there
are
some week numbers missing, want I want is a
little macro to add in
the
missing numbers and enter a 0 in colun B . For
example:

Column A Column B
Week No. Orders
1 23
2 29
5 23
6 56
8 55


This should look like:

Column A Column B
Week No. Orders
1 23
2 29
3 0
4 0
5 23
6 56
7 0
8 55

Thaks in advance.

Gareth











.

Insert rows
 

Gareth

possibly a quicker way to convert the cells to numeric:

Range("IV65536").Copy ' pick an empty cell
Range("A2:A" & LastRow).PasteSpecial _
Paste:=xlAll, _
Operation:=xlAdd, _
SkipBlanks:=False, _
Transpose:=False

Regards

Trevor


"Gareth" wrote in message
...
Tom and Trevor

I now know what the problem is. The data in column A is
imported from an Access query and is not seen by Excel as
a number.

The data is made up of week numbers calculated thus:

=Format([Orderdate],"ww")

Is there a way to 'make' it a number, either in Access or
in Excel before running the macro?

Gareth

-----Original Message-----
I generated 40 unique numbers between 1 and 1000, then
sorted them. Put the
number 1 in cell A2, the remainder of the numbers were in
3 to 42 (highest
number was 851).

Ran the first macro posted by Trevor and it worked fine.

--
Regards,
Tom Ogilvy


Trevor Shuttleworth wrote in
message
...
Gareth

works for me. Tested up to 10 numbers missing. Fills
the gaps and adds a
0
in column B.

What doesn't work for you ? The only thought is that
the data in column A
is not being seen as numeric. If I put a single quote
in front of the
numbers they then appear left aligned but that really
screws up the
calculations. The code then appears to loop but it is
actually working
and
adding lots of rows of data.

Try it with an additional test for numeric data:

Sub InsertRows()
Dim LastRow As Long
Dim i As Long
Dim InsertCount As Long
Application.ScreenUpdating = False
InsertCount = 1
Do Until InsertCount = 0
InsertCount = 0
LastRow = Range("A65536").End(xlUp).Row
For i = LastRow To 3 Step -1
If Not Application.WorksheetFunction.IsNumber
(Range("A" &
i).Value)
Then
MsgBox "Error in data"
Exit Sub
End If
If Range("A" & i).Value < Range("A" & i -
1).Value + 1 Then
Range("A" & i).EntireRow.Insert
Range("A" & i) = Range("A" & i).Offset(1,
0).Value - 1
Range("A" & i).Offset(0, 1) = 0
InsertCount = 1
End If
Next 'i
Loop
Application.ScreenUpdating = True
End Sub


Regards

Trevor


"Gareth" wrote in
message
...
Trevor

I cannot seem to get your code to work at all, just
to recap:

I want a macro to insert all numbers in ascending
order in column A,
given
that certain numbers may be missing. Against these
added numbes I would
like a 0 entered into column B.

There may be 3, 4, 5, 6, 7, 8 ..... numbers missing.

Gareth

"Trevor Shuttleworth"
wrote in message
...
Gareth

one way:

Sub InsertRows()
Dim LastRow As Long
Dim i As Long
Dim InsertCount As Long
Application.ScreenUpdating = False
InsertCount = 1
Do Until InsertCount = 0
InsertCount = 0
LastRow = Range("A65536").End(xlUp).Row
For i = LastRow To 3 Step -1
If Range("A" & i).Value < Range("A" & i -
1).Value + 1 Then
Range("A" & i).EntireRow.Insert
Range("A" & i) = Range("A" & i).Offset
(1, 0).Value - 1
Range("A" & i).Offset(0, 1) = 0
InsertCount = 1
End If
Next 'i
Loop
Application.ScreenUpdating = True
End Sub

Regards

Trevor


"Gareth" wrote in
message
...
I have 2 columns of data, A2:A? contains week
numbers in ascending
order
and
B2:B? contains orders received in that week.

I now have to put this date into a chart. This
is no problem but
there
are
some week numbers missing, want I want is a
little macro to add in
the
missing numbers and enter a 0 in colun B . For
example:

Column A Column B
Week No. Orders
1 23
2 29
5 23
6 56
8 55


This should look like:

Column A Column B
Week No. Orders
1 23
2 29
3 0
4 0
5 23
6 56
7 0
8 55

Thaks in advance.

Gareth











.