PDA

View Full Version : Solved: Copy Cell Values from Non-adjacent columns



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

Bob Phillips
05-30-2008, 02:54 PM
The worksheet is empty so it shows nothing.

Slicemahn
05-30-2008, 04:54 PM
I'm sorry. I will re-attach again.

mdmackillop
05-31-2008, 04:14 AM
I'm getting an error here. change Range to Cells in this line.


strCellCategory = .Cells(strCellLocator, OuterColumns).Value

Slicemahn
06-06-2008, 01:53 PM
Thanks for the second pair of eyes! What I also noticed is that Case 2
strCellIdentifier = "Sub-total:"

That is why the second and third columns were not being properly labeled.

Thanks again mckillop!

Cheers
CCSlice