VBA Excel - compare cell in one sheet with column in another

[si3po]

Hi folks, i'm back again and reworking a previous post that didn't get much traction.

I'm trying to loop through all values in Col 'A' of my sheet (TgtSht) and compare them with all values in Col 'A' of a worksheet (SrcSht) in an external workbook (wbSource). If the value in SrcSht Col 'A' does not exist in TrgSht and it meets the condition that SrcSht Col 'C' is "F6" and SrcSht Col 'W' is not "Archive", then the value from SrcSht Col 'A' is appended to the last row of TgtSht Col 'A'.

so i have the following code already... :

Sub CopyRow()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

    Dim SrcLastRow As Long
    Dim TgtLastRow As Long
    Dim iCounter As Integer
    Dim rslt As Range, look As Range
    Dim Cell As String
    Dim exists As Boolean
    Dim x As New Excel.Application
    Dim wbSource As Workbook
    Dim SrcSht As Worksheet
    Dim TgtSht As Worksheet

Set wbSource = x.Workbooks.Open("my_External_excel_file_name.xlsm")
Set SrcSht = wbSource.Sheets("My_File")
Set TgtSht = ThisWorkbook.Worksheets("My_File_Tab")

Set rslt = TgtSht.Range("A8:A1500")

SrcLastRow = SrcSht.Range("A" & Application.Rows.Count).End(xlUp).Row

For iCounter = 790 To SrcLastRow
exists = False

Set look = rslt.find(SrcSht.Range("A" & iCounter).Value, , xlValues, xlWhole)

TgtLastRow = TgtSht.Range("A" & Application.Rows.Count).End(xlUp).Row

 If Not look Is Nothing Then
     Cell = look.Address
     Do
     If SrcSht.Range("A" & iCounter).Value = TgtSht.Range("A:A").Value Then
        exists = True
     Exit Do
     End If
     Set look = rslt.FindNext(look)
     Loop While Not look Is Nothing And look.Address <> Cell
 End If
 If exists = False And _
        SrcSht.Range("C" & iCounter).Value = "F6" And _
        SrcSht.Range("W" & iCounter).Value <> "Archive" Then
        SrcSht.Range("A" & iCounter).Copy
        On Error Resume Next
        TgtSht.Range("A" & TgtLastRow + 1).PasteSpecial xlPasteAllExceptBorders
        End If
Next iCounter

Application.ScreenUpdating = True
Workbooks(wbSource).Close

End Sub

Now, whilst it does work the first time through, any subsequent run of the code duplicates all values in "A" of SrcSht, whether they exist in TgtSht or not. In other words, the code is simply appending all values in SrcSht where Col C = "F6" and Col W <> "Archive" rather then checking first if that value exists in TgtSht Col A already.

I know it must be a relatively simple fix, but i've been at it a while now and i cant see the wood for the trees and am officially putting out an S.O.S for assistance.

 

[Fluff]

How about

Sub si3po()
   Dim Cl As Range
   Dim Dic As Object
   Dim wbSource As Workbook
   Dim SrcSht As Worksheet, TgtSht As Worksheet
  
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual

   Set Dic = CreateObject("scripting.dictionary")
   Set wbSource = Workbooks.Open("my_External_excel_file_name.xlsm")
   Set SrcSht = wbSource.Sheets("My_File")
   Set TgtSht = ThisWorkbook.Worksheets("My_File_Tab")

   For Each Cl In TgtSht.Range("A8:A1500")
      Dic(Cl.Value) = Empty
   Next Cl
   For Each Cl In SrcSht.Range("A790", SrcSht.Range("A" & Rows.Count).End(xlUp))
      If Not Dic.Exists(Cl.Value) And Cl.Offset(, 2).Value = "F6" And Cl.Offset(, 22).Value <> "Archive" Then
         TgtSht.Range("A" & Rows.Count).End(xlUp).Offset(1).Value = Cl.Value
      End If
   Next Cl
   Application.ScreenUpdating = True
   Workbooks(wbSource).Close
End Sub

 

[si3po]

Thank you so much @Fluff this works brilliantly....thank you!

Now then, i'd like to do the same, but in reverse when closing the sheet... that is to say, when I close my worksheet I want to look in Col A on my sheet, and for each row with a value, match that value in Col A of the other sheet, and then paste the value from Col B of my worksheet into the other sheet.

i amended your code from above slightly and swapped it around very minimally to match my new requirements for this, but it keeps crashing out with a 'Type Mismatch' error... clearly i've done something wrong, but i can't tell what....any help in the right direction would be welcomed.

Sub Workbook_Close()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

   Dim Cl As Range
   Dim Dic As Object
   Dim wbTgt As Workbook
   Dim SrcSht As Worksheet, TgtSht As Worksheet
   Dim hidden As New Excel.Application

    Set Dic = CreateObject("scripting.dictionary")
    Set wbTgt = hidden.Workbooks.Open("external_workbook.xlsm")
    Set TgtSht = wbTgt.Sheets("External_WorkSheet")
    Set SrcSht = ThisWorkbook.Worksheets("My_WorkSheet")

For Each Cl In SrcSht.Range("A9:A300")
   Dic(Cl.Value) = Empty
Next Cl
For Each Cl In TgtSht.Range("A790", TgtSht.Range("A" & Rows.Count).End(xlUp))
  If Dic.exists(Cl.Value) Then
     TgtSht.Range("F").Value = Cl.Offset(, 5).Value
  End If
Next Cl
   
hidden.Quit

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

 

[Fluff]

How about

For Each Cl In SrcSht.Range("A9:A300")
   Dic(Cl.Value) = Cl.Offset(, 1).Value
Next Cl
For Each Cl In TgtSht.Range("A790", TgtSht.Range("A" & Rows.Count).End(xlUp))
  If Dic.exists(Cl.Value) Then
     Cl.Offset(, 5).Value = Dic(Cl.Value)
  End If
Next Cl

 

[si3po]

I've not tested it yet, but doesn't this bit:

Dic(Cl.Value) = Cl.Offset(, 1).Value

take the value in my Col B and consign it to the dictionary, and then this bit (code below)...looks for the Col B value from my sheet in the target sheet?

For Each Cl In TgtSht.Range("A790", TgtSht.Range("A" & Rows.Count).End(xlUp))
  If Dic.exists(Cl.Value) Then
     Cl.Offset(, 5).Value = Dic(Cl.Value)

If that's the case, then the Col B value appears only in my sheet (SrcSht) until it is copied across to the target (TgtSht), and will match when the code is run the next time.

In case i didn't explain it clearly before, when i close my sheet, if there is a match between Col A on both sheets, i want to be able to copy all of my Col B values on my sheet into Col F of the target sheet of that matching row...maybe my screenshots will help?

 

[Fluff]

This bit

Dic(Cl.Value) = Cl.Offset(, 1).Value

puts the col B value into the item associated with col A.
Theother bit of code looks for the col A value in the dictionary keys & puts the associated item into col F

 

[si3po]

I'm not sure it's working then as i can't get the any of the matched SrcSht values to have my Col B value put into Col F of the TgtSht

 

[Fluff]

Are you searching the correct ranges?

 

[si3po]

Are you searching the correct ranges?

So Col A in my sheet should search and match Col A in the target - which looking above at the code it does.

If there is a match, the Col F value from my sheet is placed into Col B of the target sheet on the same row of the matched Col A value.

and just in case, my sheet Col A runs from A9 thru A300. In the target sheet the respective Col A values begin at A790 and continue through to the last row with data on that sheet.

 

[Fluff]

If you want col F of Src to go to col B of Tgt, it should be

For Each Cl In SrcSht.Range("A9:A300")
   Dic(Cl.Value) = Cl.Offset(, 5).Value
Next Cl
For Each Cl In TgtSht.Range("A790", TgtSht.Range("A" & Rows.Count).End(xlUp))
  If Dic.exists(Cl.Value) Then
     Cl.Offset(, 2).Value = Dic(Cl.Value)
  End If
Next Cl