Finding Multiple Criteria On Different Workbook, and Looping Through Updates

[reberryjr]

I'm having trouble figuring out how to write code to find the row on mws2 where the value in column V and column Y, matches the values in column V and Y on flpws2, where column O isn't null. The other issue that I'm trying to think through, is how to loop through all of the records on flpws2, where the value in column O isn't null. My thought process there was to Do Until flpLastRow2 = "". However, I really haven't come close to grasping Do Untils or Loops yet.

The code below does find the correct record on mws2, and updates the data that I want it to. However, because the values in column V can be duplicated, I need to add that additional Find value in column Y, to ensure I'm updating the correct record.

Thoughts?

Sub MergeChelsea1LPLoans()
Application.ScreenUpdating = False
Dim flp As Workbook
Dim mws2, flpws2, flpws3, flpws4 As Worksheet
'Dim flpPath, flpFName As String
Dim flpLastRow2, UpdateRow As Long
Dim FindRow As Range
Set mws2 = ThisWorkbook.Sheets("Active_Inv")
Set flp = Workbooks.Open("[URL="file://\\crpjvffp00n2\BK"]FilePath[/URL]")
Set flpws2 = flp.Sheets("Active_Inv")
If mws2.FilterMode Then
    mws2.ShowAllData
Else
End If
If flpws2.FilterMode Then
    flpws2.ShowAllData
Else
End If
SortActiveInvSheet flpws2, "O1"
flpLastRow2 = flpws2.Range("O" & Rows.Count).End(xlUp).Row
With mws2
Set FindRow = mws2.Range("V:V").Find(What:=flpws2.Range("V2"), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows)
If Not FindRow Is Nothing Then
    UpdateRow = FindRow.Row
Else
    Exit Sub
End If
End With
If flpws2.Range("O2") = "" Then
    Exit Sub
Else
    mws2.Range("G" & UpdateRow).Value = flpws2.Range("G2").Value
    mws2.Range("H" & UpdateRow).Value = flpws2.Range("H2").Value
    mws2.Range("I" & UpdateRow).Value = flpws2.Range("I2").Value
    mws2.Range("J" & UpdateRow).Value = flpws2.Range("J2").Value
    mws2.Range("K" & UpdateRow).Value = flpws2.Range("K2").Value
    mws2.Range("L" & UpdateRow).Value = flpws2.Range("L2").Value
    mws2.Range("M" & UpdateRow).Value = flpws2.Range("M2").Value
    mws2.Range("N" & UpdateRow).Value = flpws2.Range("N2").Value
    mws2.Range("O" & UpdateRow).Value = flpws2.Range("O2").Value
End If
Application.ScreenUpdating = True
End Sub

 

[reberryjr]

@mumps...I was going through some responses on other questions, because I felt like I had a similar issue before. As it turns out, you helped me with what is a very similar issue to the one mentioned above. Is there a way to adapt the code on this link, to solve for my need above?

https://www.mrexcel.com/forum/excel...-across-two-worksheets-find-migrate-rows.html

 

[reberryjr]

So, I've gotten this code to update the same row number, but not the correct row number. Meaning, the data in flpws2 is on row 31, so it's updating row 31 on mws2. I'm pretty sure that's because I'm using Rng.Row, so I'm still trying to find how to identify the correct row (where the value in column V and AE on flpws2, match the value in column V and AE on mws2).

Sub MergeEODFLP()
Application.ScreenUpdating = False
Dim m, flp As Workbook
Dim mws2, flpws2 As Worksheet
Dim Rng As Range
Dim RngList As Object
Set m = ThisWorkbook
Set mws2 = ThisWorkbook.Sheets("Active_Inv")
Set RngList = CreateObject("Scripting.Dictionary")
On Error Resume Next
Set flp = Workbooks.Open("[URL="file://\\crpjvffp00n2\BK"]FilePath[/URL]")
Set flpws2 = flp.Sheets("Active_Inv")
'If flpws2.Range("O2") = "" Then
'    Exit Sub
'Else
For Each Rng In mws2.Range("V2", mws2.Range("V" & mws2.Rows.Count).End(xlUp))
    If Not RngList.exists(Rng.Value & Rng.Offset(0, 9)) Then
        RngList.Add Rng.Value & Rng.Offset(0, 9), Nothing
    End If
Next
For Each Rng In flpws2.Range("V2", flpws2.Range("V" & flpws2.Rows.Count).End(xlUp))
    If RngList.exists(Rng.Value & Rng.Offset(0, 9)) Then
        mws2.Range("G" & Rng.Row).Value = flpws2.Range("G" & Rng.Row).Value
        mws2.Range("H" & Rng.Row).Value = flpws2.Range("H" & Rng.Row).Value
        mws2.Range("I" & Rng.Row).Value = flpws2.Range("I" & Rng.Row).Value
        mws2.Range("J" & Rng.Row).Value = flpws2.Range("J" & Rng.Row).Value
        mws2.Range("K" & Rng.Row).Value = flpws2.Range("K" & Rng.Row).Value
        mws2.Range("L" & Rng.Row).Value = flpws2.Range("L" & Rng.Row).Value
        mws2.Range("M" & Rng.Row).Value = flpws2.Range("M" & Rng.Row).Value
        mws2.Range("N" & Rng.Row).Value = flpws2.Range("N" & Rng.Row).Value
        mws2.Range("O" & Rng.Row).Value = flpws2.Range("O" & Rng.Row).Value
    End If
Next
'End If
RngList.RemoveAll
Application.ScreenUpdating = True
MsgBox "Complete"
End Sub

 

[reberryjr]

Anyone have any ideas on this?