Paste from one workbook into the Find function of another workbook

[zombiemaster]

Hi, All...

Just looking to streamline a tedious process where I copy from cell A2 in workbook "mine" and paste it into the Cntrl-F FIND function in workbook "yours", find that data, enter today's date into column D and my initials in column E, then repeat for the next item down in column A in "mine". I know that sounds confusing so here is the step-by-step:

Activate book Mine
Copy A2
Activate book Yours
Cntrl-F
Paste into the FIND box and press enter (this is the part I can't figure out how to do)
Type today's date into column D
Type my initials into column E
Close the FIND box (if necessary?)
Activate book Mine
Move down one cell to A3
Repeat above steps

I have a couple hundred of these that I do each day, and am looking for some automation. I tried doing it myself but can't find out how to paste into the FIND box using VBA

Thanks in advance for any help!
~ZM~

 

[Fluff]

Will the col A values from Mine, be found in col A of Yours?

 

[zombiemaster]

Will the col A values from Mine, be found in col A of Yours?

Actually column J in Yours

Thanks,
~ZM~

 

[Fluff]

How about

Sub zombiemaster()
   Dim Ary As Variant, Nary As Variant
   Dim Dic As Object
   Dim i As Long
   
   Set Dic = CreateObject("scripting.dictionary")
   Dic.CompareMode = 1
   With Workbooks("Mine.xlsm").Sheets("Sheet1")
      Ary = .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Value2
   End With
   For i = 1 To UBound(Ary)
      Dic(Ary(i, 1)) = Empty
   Next i
   With Workbooks("Yours.xlsm").Sheets("Sheet1")
      Ary = .Range("J2", .Range("J" & Rows.Count).End(xlUp)).Value2
      Nary = .Range("D2:E" & .Range("J" & Rows.Count).End(xlUp)).Value2
   End With
   For i = 1 To UBound(Ary)
      If Dic.Exists(Ary(i, 1)) Then
         Nary(i, 1) = Date
         Nary(i, 2) = "ZM"
      End If
   Next i
   Workbooks("Yours.xlsm").Sheets("Sheet1").Range("D2").Resize(UBound(Nary), 2).Value = Nary
End Sub

Change workbook names & sheet names to suit.

 

[zombiemaster]

How about

Sub zombiemaster()
   Dim Ary As Variant, Nary As Variant
   Dim Dic As Object
   Dim i As Long
  
   Set Dic = CreateObject("scripting.dictionary")
   Dic.CompareMode = 1
   With Workbooks("Mine.xlsm").Sheets("Sheet1")
      Ary = .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Value2
   End With
   For i = 1 To UBound(Ary)
      Dic(Ary(i, 1)) = Empty
   Next i
   With Workbooks("Yours.xlsm").Sheets("Sheet1")
      Ary = .Range("J2", .Range("J" & Rows.Count).End(xlUp)).Value2
      Nary = .Range("D2:E" & .Range("J" & Rows.Count).End(xlUp)).Value2
   End With
   For i = 1 To UBound(Ary)
      If Dic.Exists(Ary(i, 1)) Then
         Nary(i, 1) = Date
         Nary(i, 2) = "ZM"
      End If
   Next i
   Workbooks("Yours.xlsm").Sheets("Sheet1").Range("D2").Resize(UBound(Nary), 2).Value = Nary
End Sub

Change workbook names & sheet names to suit.

I'll give that a shot on the next run - thank you, Fluff!

On a similar note from the original issue I was having, IS there a way to paste into the FIND box using VBA?

~ZM~

 

[Fluff]

Not that I'm aware of.

 

[zombiemaster]

Not that I'm aware of.

Ok good to know - thanks again!

~ZM~

 

[Fluff]

You're welcome & thanks for the feedback.

 

[zombiemaster]

Fluff, I am running a test before I head home for the day, and am getting a runtime error on this section:

Nary = .Range("D2:E" & .Range("J" & Rows.Count).End(xlUp)).Value2​

"Application-defined or object-defined error"

Also I should mention that I had to change the file extensions on both files to .xlsx from what you had above - wanted you to know that, in case it had something to do with the error.

Thoughts?

~ZM~

 

[Fluff]

Oops, keep forgetting the row

      Nary = .Range("D2:E" & .Range("J" & Rows.Count).End(xlUp).Row).Value2