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.

 

[tonyyy]

The easiest way to combine the two macros is with a Call statement, as in the below...

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
Call MergeRanges
Application.ScreenUpdating = True
End Sub

 

[tonyyy]

The easiest way to combine the two macros is with a Call statement, as in the below...

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
Call MergeRanges
Application.ScreenUpdating = True
End Sub

I'm guessing you tried to copy/paste the second macro into the first, in which case the "Dim i" declaration would prompt an error.

(Sorry, looks like this posted twice.)

 

[julievandermeulen]

Tony

Thanks so much for all your help. That worked. The only problem is sometimes in column S there is no information. Right now it leaves it blank in column G. If it doesn't find a name to match it types 'Not of File' in column G. Is there a way for it to type 'Not on File' in either of those circumstances? If not it's not a big deal. I can take the extra 10 minutes to fill that in. It still beats the 2 hours it was taking me to do everything.

Thanks again

 

[tonyyy]

Julie,

Column S? In the sample sheet we've been using Column O (ie, Column 15), so that's what I'll continue to use. You can edit it if needed.

Please delete this line:

If Cells(i, 3) & Cells(i, 4) = Cells(j, 9) & Cells(j, 10) Then

And replace it with this line:

If Cells(i, 3) & Cells(i, 4) = Cells(j, 9) & Cells(j, 10) And Cells(j, 15) <> "" Then

Cheers,

Tony

 

[julievandermeulen]

Julie,

Column S? In the sample sheet we've been using Column O (ie, Column 15), so that's what I'll continue to use. You can edit it if needed.

Please delete this line:

If Cells(i, 3) & Cells(i, 4) = Cells(j, 9) & Cells(j, 10) Then

And replace it with this line:

If Cells(i, 3) & Cells(i, 4) = Cells(j, 9) & Cells(j, 10) And Cells(j, 15) <> "" Then

Cheers,

Tony

haha yes you are right. I was looking at the original report I pulled before I did any formatting and asked for help because I got stuck. It is column O.

Thanks so much! That works. Now I just need to try it on an original report and not my practice. Then I will mark it as helped & completed.