Slicemahn
05-30-2008, 11:54 AM
Hello VBA Express Nation!
I have need for a second pair of eyes, in which I am having difficulty in renaming cells with categories. For example, for all items grouped in ADSL, it is logical that the total sum of the contents within this category would be "Total:ADSL". However, in my code I have only been able to get "Total:" along with the row value of the cell I would like. Below is my code along with an attachment showing the worksheet I am using. I hope someone with eyes less red than I will find where I am going wrong.
Sub Ignition()
Dim EndRow As Long
Dim InnerRowSkip As Integer
Dim OuterColumns As Integer
Dim strCellLocator As Integer
Dim strCellCategory As String
Dim strCellIdentifier As String
With ActiveSheet
EndRow = .Cells(65536, 1).End(xlUp).Row
For OuterColumns = 1 To 2
Select Case OuterColumns
Case 1
strCellIdentifier = "Total:"
Case 2
strCellIdentifer = "Sub-total:"
End Select
For InnerRowSkip = 4 To EndRow
If Left(.Cells(InnerRowSkip, OuterColumns + 1), 6) = strCellIdentifier _
Or Left(.Cells(InnerRowSkip, OuterColumns + 1), 10) = strCellIdentifier Then
strCellLocator = .Cells(InnerRowSkip, OuterColumns).End(xlUp).Row
strCellCategory = .Range(strCellLocator, OuterColumns).Value
.Cells(InnerRowSkip, OuterColumns + 1).Value = strCellIdentifier & " " & strCellCategory
End If
Next InnerRowSkip
Next OuterColumns
InnerRowSkip = 4
End With
End Sub
I have need for a second pair of eyes, in which I am having difficulty in renaming cells with categories. For example, for all items grouped in ADSL, it is logical that the total sum of the contents within this category would be "Total:ADSL". However, in my code I have only been able to get "Total:" along with the row value of the cell I would like. Below is my code along with an attachment showing the worksheet I am using. I hope someone with eyes less red than I will find where I am going wrong.
Sub Ignition()
Dim EndRow As Long
Dim InnerRowSkip As Integer
Dim OuterColumns As Integer
Dim strCellLocator As Integer
Dim strCellCategory As String
Dim strCellIdentifier As String
With ActiveSheet
EndRow = .Cells(65536, 1).End(xlUp).Row
For OuterColumns = 1 To 2
Select Case OuterColumns
Case 1
strCellIdentifier = "Total:"
Case 2
strCellIdentifer = "Sub-total:"
End Select
For InnerRowSkip = 4 To EndRow
If Left(.Cells(InnerRowSkip, OuterColumns + 1), 6) = strCellIdentifier _
Or Left(.Cells(InnerRowSkip, OuterColumns + 1), 10) = strCellIdentifier Then
strCellLocator = .Cells(InnerRowSkip, OuterColumns).End(xlUp).Row
strCellCategory = .Range(strCellLocator, OuterColumns).Value
.Cells(InnerRowSkip, OuterColumns + 1).Value = strCellIdentifier & " " & strCellCategory
End If
Next InnerRowSkip
Next OuterColumns
InnerRowSkip = 4
End With
End Sub