Macro with merged cells

[julievandermeulen]

I'm looking to write a macro that will

Search the Last Names in column C starting in row 4 and match it to the Last Names in column I starting in row 4.
Then search the First Names in column D starting in row 4 and match it to the First Names in column J starting in row 4.
Then making sure the Last and First name match take the information from Column O starting in row 4 and putting the information in column G starting in row 4.

The problem I'm having is the names in column C & D are sometimes merged in 2 or 3 rows.
Ex. C4:C6 = Smith
D4:D6 = Joe

I'm not sure how to unmerge the cells and keep it inline with the correct information.

 

[mumps]

I would suggest that you re-design your sheet without using merged cells. They almost always create problems for macros. Once you have done this, use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[tonyyy]

Hi Julie... nice to see you again.

The value in a merged range will always be in the top left cell, even if it started elsewhere when the merged range was created. It might be beneficial to see if unmerging will keep the cells in line - on a copy or test worksheet only please. You can unmerge all the merged ranges in Column D for example with the following...

Sub UnmergeColumn()
    Columns(4).Unmerge
End Sub

Whether or not the results line up correctly will determine how to code a solution. In either case, as @mumps suggests, posting sample data is really necessary to code it properly.

Cheers,

Tony

 

[bebo021999]

A tip to refer to the merge cell in , for example, C4:C6 = Smith
In row 4, i.e, cell G4:
=LOOKUP(2,1/($C$4:$C6<>""),$C$4:$C6)

Then to compare C4:C6 with I4 and D4:D6 with J4, in G4:

=IF(AND($I4=LOOKUP(2,1/($C$4:$C6<>""),$C$4:$C6),$J4=LOOKUP(2,1/($D$4:$D6<>""),$D$4:$D6)), $I4 & " " & $J4,"")

Drag G4 down

 

[julievandermeulen]

Hi Julie... nice to see you again.

The value in a merged range will always be in the top left cell, even if it started elsewhere when the merged range was created. It might be beneficial to see if unmerging will keep the cells in line - on a copy or test worksheet only please. You can unmerge all the merged ranges in Column D for example with the following...

Sub UnmergeColumn()
    Columns(4).Unmerge
End Sub

Whether or not the results line up correctly will determine how to code a solution. In either case, as @mumps suggests, posting sample data is really necessary to code it properly.

Cheers,

Tony

Hello Tony

Happy New Year! Hope your year is going well.

Yes that macro unmerged the column.

I've tried to attach a screen shot. Hopefully it worked. I'm not good at that stuff so let me know if I need to do something differently.

Julie

 

[mumps]

You attached a picture rather than a screen shot and it is hard to work with a picture. Please attach a screen shot (not a picture) of your sheet or upload a copy of your file. Please refer to Post #2.

 

[tonyyy]

Hey Julie...
You might try the following:

Sub ConcatenateAndFill()
Dim i As Long, j As Long

Application.ScreenUpdating = False
For i = 4 To Cells(Rows.Count, "C").End(xlUp).Row
    For j = 4 To Cells(Rows.Count, "I").End(xlUp).Row
        If Cells(i, 3) <> "" Then
            If Cells(i, 3) & Cells(i, 4) = Cells(j, 9) & Cells(j, 10) Then
                Cells(i, 7) = Cells(j, 15)
                Exit For
            End If
        End If
    Next j
Next i
Application.ScreenUpdating = True
End Sub

This will pair the names in Columns C and D and compare them to the names in Columns I and J, and if there's a match it'll move the Date from Column O to Column G.

Secondarily, if you want to Merge the cells with the Dates to match the merged format of the names you can use the code below.

Sub MergeRanges()
Dim i As Long
Dim rng As Range
Dim adr As String

Application.ScreenUpdating = False
For i = 4 To Cells(Rows.Count, "C").End(xlUp).Row
    Set rng = Cells(i, 3)
    If rng.MergeCells Then
        Set rng = rng.MergeArea
        adr = rng.Address
        adr = Replace(adr, "C", "G")
        With Range(adr)
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            .MergeCells = True
        End With
        i = i + rng.Rows.Count - 1
    End If
Next i
Application.ScreenUpdating = True
End Sub

Ya, I'm not a big fan of Merged Cells, but sometimes you gotta work with what you're given.

Tony

 

[julievandermeulen]

Hey Julie...
You might try the following:

Sub ConcatenateAndFill()
Dim i As Long, j As Long

Application.ScreenUpdating = False
For i = 4 To Cells(Rows.Count, "C").End(xlUp).Row
    For j = 4 To Cells(Rows.Count, "I").End(xlUp).Row
        If Cells(i, 3) <> "" Then
            If Cells(i, 3) & Cells(i, 4) = Cells(j, 9) & Cells(j, 10) Then
                Cells(i, 7) = Cells(j, 15)
                Exit For
            End If
        End If
    Next j
Next i
Application.ScreenUpdating = True
End Sub

This will pair the names in Columns C and D and compare them to the names in Columns I and J, and if there's a match it'll move the Date from Column O to Column G.

Secondarily, if you want to Merge the cells with the Dates to match the merged format of the names you can use the code below.

Sub MergeRanges()
Dim i As Long
Dim rng As Range
Dim adr As String

Application.ScreenUpdating = False
For i = 4 To Cells(Rows.Count, "C").End(xlUp).Row
    Set rng = Cells(i, 3)
    If rng.MergeCells Then
        Set rng = rng.MergeArea
        adr = rng.Address
        adr = Replace(adr, "C", "G")
        With Range(adr)
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            .MergeCells = True
        End With
        i = i + rng.Rows.Count - 1
    End If
Next i
Application.ScreenUpdating = True
End Sub

Ya, I'm not a big fan of Merged Cells, but sometimes you gotta work with what you're given.

Tony

Thanks Tony

They both work great separately.
I need to run one first and then run the second. Is there away to run them as 1. It gets hung up on dim i as long.

Also I have another question. Is there a way to get the cells in column G (after they are merged to match column C & D) if they are blank to have Not on File typed into it? Can I do that with conditional format in any way?

 

[tonyyy]

This should address the "Not on File" case...

Sub ConcatenateAndFill()
Dim i As Long, j As Long

Application.ScreenUpdating = False
For i = 4 To Cells(Rows.Count, "C").End(xlUp).Row
    For j = 4 To Cells(Rows.Count, "I").End(xlUp).Row
        If Cells(i, 3) <> "" Then
            If Cells(i, 3) & Cells(i, 4) = Cells(j, 9) & Cells(j, 10) Then
                Cells(i, 7) = Cells(j, 15)
                Exit For
            Else
                Cells(i, 7) = "Not on File"
            End If
        End If
    Next j
Next i
Application.ScreenUpdating = True
End Sub

"Is there away to run them as 1. It gets hung up on dim i as long."

A Call statement will run the second macro from the first...

Call MergeRanges

But I'm puzzled by the "hung up on dim i as long". Is there an error message? Even when running them separately?

 

[julievandermeulen]

This should address the "Not on File" case...

Sub ConcatenateAndFill()
Dim i As Long, j As Long

Application.ScreenUpdating = False
For i = 4 To Cells(Rows.Count, "C").End(xlUp).Row
    For j = 4 To Cells(Rows.Count, "I").End(xlUp).Row
        If Cells(i, 3) <> "" Then
            If Cells(i, 3) & Cells(i, 4) = Cells(j, 9) & Cells(j, 10) Then
                Cells(i, 7) = Cells(j, 15)
                Exit For
            Else
                Cells(i, 7) = "Not on File"
            End If
        End If
    Next j
Next i
Application.ScreenUpdating = True
End Sub

"Is there away to run them as 1. It gets hung up on dim i as long."

A Call statement will run the second macro from the first...

Call MergeRanges

But I'm puzzled by the "hung up on dim i as long". Is there an error message? Even when running them separately?

Sorry I'm not very good at explaining.
Both macros work great when run separately.
When I combined them and tried to run together as 1. It stops on dim i as long in the merged range section.