Issues with Pasting Unique Text into Find Field in Macro

To boil this down as best I can, I have 2 separate workbooks. I have copied the 2 columns of data from Workbook 2 into sheet 2 of Workbook 1. I used the search function in the best manner I could. No one seemed to have the specific issue I am having atm. It is very possible I searched for the wrong terms, so if a post does exist with my specific issue, please lmk.

What I have tried to do with the below Macro is the following:

- The value of Column A on Sheet 1 matches a value in Column G of sheet 2.
- On Sheet 2, the value of Column H that corresponds with the matching value found on Sheet 2 I want to be copied/pasted, inserted, linked, mirrored (etc.) into Column B of sheet 1.

I added a Do While loop into the macro to run down through the list until it reaches an empty row, but the problem I'm running into is the Find function needs an explicit value (i.e. "Sample"). I cannot seem to locate a proper replacement such as "ActiveSheet.Paste" to insert the copied date into the Find searchbox.

Am I going about this the wrong way? Should I scrap the code I've made for something completely different?

Please Login or Register  to view this content.

Can you post a sample file? If I understand correctly, you may not need VBA for this, and could possible use an Index Match formula to get the value you want.

Originally Posted by JeteMc

Try pasting the following into cell A3 on sheet 1: =IFERROR(INDEX('Sheet 2'!A$3:A$11,MATCH(C3,'Sheet 2'!B$3:B$11,0)),"")
Let us know if you have any questions.

Took a little time modifying the function to adapt to what I needed, but this performed excellently!

Is there a chance that it can return "N/A" or something if it doesn't find the value? Currently, it only leaves a blank cel which works if not.

Either way, thanks again for this! It helps out extremely!

Originally Posted by averagejoe365

Is there a chance that it can return "N/A" or something if it doesn't find the value? Currently, it only leaves a blank cel which works if not.

You may have already figured it out, but yes just change the ending quotes at the end for the formula from "" to "N/A"

Originally Posted by ptmuldoon

You may have already figured it out, but yes just change the ending quotes at the end for the formula from "" to "N/A"

Yes, I figured it out after messing with it again for a little while. Thanks again for the quick response!