Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
insert rows in a worksheet that do not change adjoining rows | Excel Discussion (Misc queries) | |||
How do i insert blank rows between data that is thousands of rows | Excel Discussion (Misc queries) | |||
Insert rows: Formats & formulas extended to additonal rows | Excel Worksheet Functions | |||
How do I insert blank rows between rows in completed worksheet? | Excel Discussion (Misc queries) | |||
How do i insert of spacer rows between rows in large spreadsheets | Excel Discussion (Misc queries) |