• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to find value in a range & copy paste

ThrottleWorks

ThrottleWorks

Excel Ninja
Hi,

I have 2 files.

File 1 has some text values, range is ("a2:a477").

I have file 2, I have to find each cell from File 1 in File 2.
If there are any results I have to copy that value & paste in File 1.

For example,

File 1, range A2 value = sachin
If I find Sachin in file 2, value in the File 1 Range B2 will be Sachin.

I am trying to do this by using following code.
I do not know, how to copy the value if the value is present.

If the value is not present, the macro will give bug, so I am using On Error Resume Next. (not given here, but is in the original code).

Can anyone help me in this please.

Code: 
Sub FindValue()
   
    Dim MyRng As Range
    Set MyRng = Range("a2:a477")
   
    Dim MyWb As Workbook
    Dim MyMacro As Workbook
   
    Set MyWb = Workbooks("aaa.xlsx")
    Set MyMacro = ActiveWorkbook
   
   
        For Each rn In MyRng
            rn.Copy
            MyWb.Activate
                Cells.Find(What:=rn.Value, After:=ActiveCell, LookIn:= _
                xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
                xlNext, MatchCase:=False, SearchFormat:=False).Activate
        Next
       
End Sub
 
If the value in A1, in File 1, is "Sachin"
and in file 2, somewhere value is "Sachinbizboy", what should be the value is File 1->B2
"Sachin" or "Sachinbizboy"!!
 
Hi Debraj, the value should be sachinbizboy, ideally it will be sachin only.

But getting sachinbizboy will help, thanks a lot for the help.
 
Hi Xiq,

Sorry about the confusion, :(

I am using the formula in following way.

=IF(ISERR(MATCH($A2,'[aaa.xlsx]abc Download'!G2:G8000,)),"",$A2)

But I think the formula is giving me wrong results.
I will tell you what I am doing.

There are 2 files, one file is "FindMacro"
In this file, range A2:A477 there are few names.

I have another file, aaa.xlsx.
In this file, there is a tab, abc Download.

This tab has around 8000 records.
I have to find each name from FindMacro (477 names in total) in

aaa.xlsx, abc Download, column G, because the names are in column G.

Could you please tell me what mistake I am doing in applying your formula.

Thanks for the help.
 
Well it can depend on a couple of things. But first of all, try to fix the row reference like so G$2:G$8000:
Code: 
=IF(ISERR(MATCH($A2,'[aaa.xlsx]abc Download'!G$2:G$8000,)),"",$A2)
 
Xiq, thanks for the help, still it is not working.

The formula is showing 100% result, idealy there should be hardly 10-20.

I am trying to understand my mistake, will revert soon.

Have a nice day ahead.
 
Xiq, no luck yet.

To check my working I did the following thing.

Both the data in one file only, sheet1 & sheet2

Sheet 1, range A2 to A477 is the table, I put following formula in sheet1 Cell C2.

=IF(ISERR(MATCH($A2,Sheet2!A$2:A$8000,)),"",$A2)

Now in the sheet2, range A$2:A$8000 if I put sachin anywhere, the formula should return sachin.

But the formula result is sachin even if there is no value in the range.
 
@Sachin

write the Xiq formula in B2 of FindMacro

before you write the code please open both files and write the formula in B2 of FindMacro File

Code: 
=IF(ISERR(MATCH($A2,'[aaa.xlsx]abc Download'!$G:$G,)),"",$A2)

for your knowledge i have attached 2 files please download and check, if any problem please inform

Thanks
 

Attachments

  • look.xlsx
    8.2 KB · Views: 2
  • FindMacro.xlsx
    9.9 KB · Views: 2
Hi sgmpatnaik, it is not working.

The formula is showing 100% result.
If I am finding same value manually in aaa.xlsx I am not getting any results.
 
Sachine,
Can you upload a sample file?

Here is another approach:
Code: 
=IF(COUNTIF('[aaa.xlsx]abc Download'!$G:$G,$A2)<>0,$A2,"")
 
@ Sachin: If you want to lookup your values from File1 Col.A to File2 Col. G..so you can use the vlookup in Col.B of the File1 and support it with an iserror formula to avoid the errors and I think this should suffice...

Let me know if this does not help....Maybe we can find another option(s) then....
 
Hi Sachi..

Try this...

Code: 
Sub CopyData()
Dim lookuprng As Range, pastefile As Worksheet
    lookupfile = Application.GetOpenFilename("Excel File (*.xlsx), *.xlsx")
    If lookupfile <> False Then
        Workbooks.Open (lookupfile)
        Set lookuprng = Application.InputBox("Select the sheet & Range.. to check", "LookupRange", "abc Download!G:G", , , , , 8)
        With ThisWorkbook.Sheets(1)
            .Range("B1:B" & .Range("A" & .Rows.Count).End(xlUp).Row).Formula = "=Iferror(Index(" & lookuprng.Address(external:=True) _
                & ", Match($A1 & ""*"", " & lookuprng.Address(external:=True) & ", False)),"""")"
        End With
    End If
End Sub
 
Hi Sachin ,

I am not sure I have understood your requirement , but can you check out the attached file ?

Run the macro called Insert_Matches.

Of course , we will have to extend this to two workbooks and see whether it works.

Narayan
 

Attachments

  • Sachin_Example.xlsm
    14.2 KB · Views: 6
Sorry for late reply, got stuck with another work, will try to explain why I have to work this way.

Will share the results ASAP.
 
Hi Debraj, thanks a lot for the help & your valuable time.

This is working, I got only 2 valid results from the data which were not populated by vlookup.
I am still checking this, but I think there should not be any problem.

Narayan Sir, sorry for the confuion & not explaining it properly.
Sir I checked your file, thanks a lot for the help, just wanted to share one observation.

For example, the Name A is not present in List2 ( I deleted for testing purpose), the macro was populating NameV.
May be I am doing something wrong, I will recheck it.

I will explain why I was not taking vlooup & trying this method.
Please give me some time, stuck with another work. :(

Once again, thanks a lot Debraj, Xiq, Narayan Sir, sgmpatnaik, & Abhijeet. :)

P.S. - Xiq ! the latest formula you have provided is working.
The results are matching with Debraj's macro.

Thanks a lot for help, lots of material to study from this post. :)
 
Hi Sachin ,

Sorry but the case of the mismatch had not been considered at all. I wanted you to confirm whether this approach was acceptable to you.

Check the file now.

Narayan
 

Attachments

  • Sachin_Example.xlsm
    14.5 KB · Views: 6
Yes Sir, I thought so, I fortgot to tell you that there can be mismatches also, my bad.

I am checking the file, will share the results ASAP, thanks a lot for the help.

P.S. - Sir, it is working absolutely fine, I just checked it.

Thanks a lot & have a nice weekend. :)
 
Hi, sachinbizboy!
More than 500 posts, so not any newbie but and old member. You yet know the routine, so a topic that required 24 posts to be solved maybe should have required 3 or 4 if you had provided the sample files. Take care for next time. Thank you.
Regards!
 
You must log in or register to reply here.
Back
Top