Comparing Columns

[jstevens]

I am trying to expand my code to include a concatenation of two columns.

I have two groups of columns each containing the following headings: Accounts, Product,Affiliate,Measure and Total Amount. Column A would start the first group with "Accounts" and Column I would be the start of the second group again with "Accounts".

I'm considering using a Union of two ranges to do this but am having difficulty with the concatenation. I'm not sure how to concatenate Columns A & D (oCell) in the line of code starting with "For Each oCell In rCol_From".

Code: Select all

Sub CompareCol()
Dim rCol_From As range, rCol_To As range, i As range

Set rCol_From = Union(range("A3", range("A" & Rows.Count).End(xlUp)), range("D3", range("D" & Rows.Count).End(xlUp)))
Set rCol_To = Union(range("I3", range("I" & Rows.Count).End(xlUp)), range("L3", range("L" & Rows.Count).End(xlUp)))

'First Pass on LocalHost to Production
'Basis: Accounts/Measure Combination
application.StatusBar = "Validating LocalHost to Production Account Structure..."
    For Each oCell In rCol_From                                                                                   'How do I include the concatenation of Column A & D

        If Not rCol_To.Find(What:=oCell) Is Nothing Then                                                
                oFindRow = rCol_To.Find(What:=oCell, LookAt:=xlWhole).Row

                If oFindRow <> oCell.Row Then
                    range("I" & oFindRow & ":M" & oFindRow).Cut
                        range("I" & oCell.Row).Insert Shift:=xlDown
                End If

         End If
    Next oCell
Exit sub

Thanks for taking a look,
John

[HansV]

Using Union makes no sense here.

You should create formulas in columns F and in column N.

In F2: =A2&"|"&D2
In N2: =I2&"|"&L2

(The | is just a separator, you could use another character if you like)

Your code can then use columns F and N to compare the values.

[jstevens]

Hans,

Sometimes simplicity works best.

Regards,
John