B
Buyer
I have a problem. I have a large set of data whose first column downloads a
manufactured part that is tied to a list of attributes. For example the
first 10 rows in column 1 are all the same number, e.g. 12345 and then the
each row in column 2, 1 thru 10 is a different numbered attribute such as Row
1 Column 2 is 890 and Row 2 Column 2 is 899, etc. After the initial 10 rows
it starts with another manufacturers part that might go for 20 rows, etc. I
want to insert two blank rows and sum the 10 rows in Column 2, and then the
next X rows for the next part number. I thought I had it figured out with
the below macro, but for some reason after the rows are inserted I run the
sum macro and it stops at row 4,460 even though I have more data to sum. I
scrubbed the data to see if it is an anomolly with the row or the data in
that cell but there doesn't seem to be a problem with it. Here are my
macros: Can someone help me out or explain why it's stopping at row 4,460?
Sub SumAndSeparate()
StartRow = 2 'Change the 2 to the row actual data start
DataColumn = 1 'Change the 1 to the column where your data is
i = StartRow + 1
While Cells(i, DataColumn) <> ""
If Cells(i, DataColumn) <> Cells(i - 1, DataColumn) Then
Cells(i, DataColumn).EntireRow.Insert
Cells(i, DataColumn).EntireRow.Insert ' a 2nd blank row added
i = i + 2
End If
i = i + 1
Wend
End Sub
Sub insert_sum_values()
Dim sum_of_range, tmp
For i = 1 To 32
If i = 1 Or i = 11 Or i = 16 Or i = 17 Or i = 18 Or i = 19 Or i = 20
Or i = 21 Or i = 22 Or i = 23 Or i = 24 Or i = 25 Or i = 26 Or i = 27 Or i =
28 Or i = 29 Or i = 30 Or i = 31 Or 32 Then
'column numbers where sums required
Cells(3, i).Select
'first cell at top of range to be summed
Do
Range(ActiveCell, ActiveCell.End(xlDown)).Select
tmp = ActiveCell.Value
If tmp <> "" Then
sum_of_range = "=SUM(" & Selection.Address & ")"
ActiveCell.End(xlDown).Offset(1, 0).Value = sum_of_range
ActiveCell.End(xlDown).Offset(2, 0).Select
Else
sum_of_range = ""
End If
Loop Until sum_of_range = ""
End If
Next i
End Sub
manufactured part that is tied to a list of attributes. For example the
first 10 rows in column 1 are all the same number, e.g. 12345 and then the
each row in column 2, 1 thru 10 is a different numbered attribute such as Row
1 Column 2 is 890 and Row 2 Column 2 is 899, etc. After the initial 10 rows
it starts with another manufacturers part that might go for 20 rows, etc. I
want to insert two blank rows and sum the 10 rows in Column 2, and then the
next X rows for the next part number. I thought I had it figured out with
the below macro, but for some reason after the rows are inserted I run the
sum macro and it stops at row 4,460 even though I have more data to sum. I
scrubbed the data to see if it is an anomolly with the row or the data in
that cell but there doesn't seem to be a problem with it. Here are my
macros: Can someone help me out or explain why it's stopping at row 4,460?
Sub SumAndSeparate()
StartRow = 2 'Change the 2 to the row actual data start
DataColumn = 1 'Change the 1 to the column where your data is
i = StartRow + 1
While Cells(i, DataColumn) <> ""
If Cells(i, DataColumn) <> Cells(i - 1, DataColumn) Then
Cells(i, DataColumn).EntireRow.Insert
Cells(i, DataColumn).EntireRow.Insert ' a 2nd blank row added
i = i + 2
End If
i = i + 1
Wend
End Sub
Sub insert_sum_values()
Dim sum_of_range, tmp
For i = 1 To 32
If i = 1 Or i = 11 Or i = 16 Or i = 17 Or i = 18 Or i = 19 Or i = 20
Or i = 21 Or i = 22 Or i = 23 Or i = 24 Or i = 25 Or i = 26 Or i = 27 Or i =
28 Or i = 29 Or i = 30 Or i = 31 Or 32 Then
'column numbers where sums required
Cells(3, i).Select
'first cell at top of range to be summed
Do
Range(ActiveCell, ActiveCell.End(xlDown)).Select
tmp = ActiveCell.Value
If tmp <> "" Then
sum_of_range = "=SUM(" & Selection.Address & ")"
ActiveCell.End(xlDown).Offset(1, 0).Value = sum_of_range
ActiveCell.End(xlDown).Offset(2, 0).Select
Else
sum_of_range = ""
End If
Loop Until sum_of_range = ""
End If
Next i
End Sub