jmthompson
Well-known Member
- Joined
- Mar 31, 2008
- Messages
- 966
Good afternoon,
So I have spreadsheet data in columns A-L. Column A includes an account name and number. What I want to do is group the data by account type, which is determined by the 1st digit of the account number.
I am going to post my ugly, dirty-laundry, frankenstein code below. It's part of a much bigger macro, but at this point in the macro , a row has been inserted beneath each group of accounts and entered the name of the account type in column a.
This code is supposed to "group" the accounts by type. Here's the kicker, not all of the account types will be present each time this code is run. As it is written now, the code will work fine if every other account type is missing (accounts go from 200000 series to 400000 series to 600000 series and so on), but not if consecutive account types are missing (accounts go from 200000 series to the 500000 series).
How do I make this more flexible?
So I have spreadsheet data in columns A-L. Column A includes an account name and number. What I want to do is group the data by account type, which is determined by the 1st digit of the account number.
I am going to post my ugly, dirty-laundry, frankenstein code below. It's part of a much bigger macro, but at this point in the macro , a row has been inserted beneath each group of accounts and entered the name of the account type in column a.
This code is supposed to "group" the accounts by type. Here's the kicker, not all of the account types will be present each time this code is run. As it is written now, the code will work fine if every other account type is missing (accounts go from 200000 series to 400000 series to 600000 series and so on), but not if consecutive account types are missing (accounts go from 200000 series to the 500000 series).
How do I make this more flexible?
Code:
For Each c In Range("A5:A" & lastRow + 8)
If c = "" Then
Select Case Left(Right(c.Offset(-1, 0), 7), 1)
Case Is = 1
c.Value = "Balance Sheet Accounts"
Case Is = 2
c.Value = "Liability Accounts"
Case Is = 3
c.Value = "Equity Accounts"
Case Is = 4
c.Value = "Revenue Accounts"
Case Is = 5
c.Value = "Expense Accounts"
Case Is = 6
c.Value = "Other Operating Accounts"
Case Is = 7
c.Value = "Non-Operating Accounts"
Case Is = 9
c.Value = "Statistical Accounts"
End Select
End If
Next c
lastRow = Range("A" & Rows.Count).End(xlUp).Row
MyBreak1 = 0
MyBreak2 = 0
MyBreak3 = 0
MyBreak4 = 0
MyBreak5 = 0
MyBreak6 = 0
MyBreak7 = 0
MyBreak8 = 0
For Each c In Range("B5:B" & lastRow)
If c = "" Then
Select Case c.Offset(, -1)
Case Is = "Balance Sheet Accounts"
MyBreak1 = c.Row
Case Is = "Liability Accounts"
MyBreak2 = c.Row
Case Is = "Equity Accounts"
MyBreak3 = c.Row
Case Is = "Revenue Accounts"
MyBreak4 = c.Row
Case Is = "Expense Accounts"
MyBreak5 = c.Row
Case Is = "Other Operating Accounts"
MyBreak6 = c.Row
Case Is = "Non-Operating Accounts"
MyBreak7 = c.Row
Case Is = "Statistical Accounts"
MyBreak8 = c.Row
End Select
End If
Next c
If MyBreak1 = 0 Then
MyBreak1a = 5
Else
MyBreak1a = MyBreak1 + 1
End If
If MyBreak1 = 0 Then
MyBreak1 = MyBreak2
End If
If MyBreak2 = 0 Then
MyBreak2a = MyBreak1 + 1
Else
MyBreak2a = MyBreak2 + 1
End If
If MyBreak2 = 0 Then
MyBreak2 = MyBreak3
End If
If MyBreak3 = 0 Then
MyBreak3a = MyBreak2 + 1
Else
MyBreak3a = MyBreak3 + 1
End If
If MyBreak3 = 0 Then
MyBreak3 = MyBreak4
End If
If MyBreak4 = 0 Then
MyBreak4a = MyBreak3 + 1
Else
MyBreak4a = MyBreak4 + 1
End If
If MyBreak4 = 0 Then
MyBreak4 = MyBreak5
End If
If MyBreak5 = 0 Then
MyBreak5a = MyBreak4 + 1
Else
MyBreak5a = MyBreak5 + 1
End If
If MyBreak5 = 0 Then
MyBreak5 = MyBreak6
End If
If MyBreak6 = 0 Then
MyBreak6a = MyBreak5 + 1
Else
MyBreak6a = MyBreak6 + 1
End If
If MyBreak6 = 0 Then
MyBreak6 = MyBreak7
End If
If MyBreak7 = 0 Then
MyBreak7a = MyBreak6 + 1
Else
MyBreak7a = MyBreak7 + 1
End If
If MyBreak7 = 0 Then
MyBreak7 = MyBreak8
End If
If MyBreak8 = 0 Then
MyBreak8 = lastRow
End If
Rows("5:" & MyBreak1 - 1).Rows.Group
Rows(MyBreak1a & ":" & MyBreak2 - 1).Rows.Group
Rows(MyBreak2a & ":" & MyBreak3 - 1).Rows.Group
Rows(MyBreak3a & ":" & MyBreak4 - 1).Rows.Group
Rows(MyBreak4a & ":" & MyBreak5 - 1).Rows.Group
Rows(MyBreak5a & ":" & MyBreak6 - 1).Rows.Group
Rows(MyBreak6a & ":" & MyBreak7 - 1).Rows.Group
Rows(MyBreak7a & ":" & MyBreak8 - 1).Rows.Group
