VBA Vlookup different Workbook copy/paste result

SuperBaby

New Member
Joined
Apr 17, 2013
Messages
24
Hi,

(sorry for duplicate the Post, I get suggested to change title)

I would like to ask for <ACRONYM title="visual basic for applications">vba</ACRONYM> code to search the given value in another workbook and return the corresponding cell back to the original workbook. Can anyone help please?

Workbook1
ABC
1AB6675-30Confirm

<TBODY>
</TBODY>


Workbook2
ABC
1AB6675-30.300001
2AC53555-25.100002

<TBODY>
</TBODY>


I would like to have cell A1 in workbook1 to be searched in column A workbook2 and return value in column B to workbook 1 column C.

Thank you very much.​
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
I Assumed That The Data In Both Workbooks Is In The First Sheet,If It's Not,Change The Sheet Number In The Code In This Part "Sheets(1)" Or Type The Sheet Name.
Code:
Sub Search()
i = Workbooks("Workbook1").Sheets(1).Range("a1").Value
m = Application.Match(i, Workbooks("Workbook2").Sheets(1).Range("A:A"), 0)
If IsError(m) = True Then
   MsgBox "The Value Is Not Found"
   Exit Sub
End If
Workbooks("Workbook2").Activate
Sheets(1).Range("b" & m).Select
Selection.Copy
Workbooks("Workbook1").Activate
Sheets(1).Range("c1").PasteSpecial
Application.CutCopyMode = False
End Sub
ZAX
 
Upvote 0
I Assumed That The Data In Both Workbooks Is In The First Sheet,If It's Not,Change The Sheet Number In The Code In This Part "Sheets(1)" Or Type The Sheet Name.
Code:
Sub Search()
i = Workbooks("Workbook1").Sheets(1).Range("a1").Value
m = Application.Match(i, Workbooks("Workbook2").Sheets(1).Range("A:A"), 0)
If IsError(m) = True Then
   MsgBox "The Value Is Not Found"
   Exit Sub
End If
Workbooks("Workbook2").Activate
Sheets(1).Range("b" & m).Select
Selection.Copy
Workbooks("Workbook1").Activate
Sheets(1).Range("c1").PasteSpecial
Application.CutCopyMode = False
End Sub
ZAX

Hi Zax,

Thank you so much for the code. Sorry for didnt reply you at once, I was not at work on weekend. I will try you code and will tell you if this is working. again thank you so much.
 
Upvote 0
Hi Dear,

As i see the value in Col A ie AB6675-30 in workbook1 and the value in col A in workbook 2 ie AB6675-30.3 are different so do you want the macro that consider only integer part not the decimal part for searching the value.
Please correct me so that i can provide you exact solution.

Regards
Prince
 
Upvote 0
Hi Dear,

As i see the value in Col A ie AB6675-30 in workbook1 and the value in col A in workbook 2 ie AB6675-30.3 are different so do you want the macro that consider only integer part not the decimal part for searching the value.
Please correct me so that i can provide you exact solution.


Regards
Prince
Hi Prince,

Thanx for your care. Yes, I would like it to consider the decimal part too. Would you please ?
 
Upvote 0
Hi Zax,

I have tried the code and it's error with message "400", would you please let me know how can I fix this ?
 
Upvote 0
Sorry For The Late Reply But I Haven't Checked The Subscribed Threads For A While...
Anyway,Try This Code.
Code:
Sub Search()
i = Workbooks("Workbook1").Sheets(1).Range("a1").Value
m = Application.Match(i, Workbooks("Workbook2").Sheets(1).Range("A:A"), 0)
If IsError(m) = True Then
   MsgBox "The Value Is Not Found"
   Exit Sub
End If
Workbooks("Workbook2").Activate
Sheets(1).Select
Range("B" & m).Select
Selection.Copy
Workbooks("Workbook1").Activate
Sheets(1).Select
Range("C1").PasteSpecial
Application.CutCopyMode = False
End Sub
ZAX
 
Upvote 0
Hi Zax,

Thank you so much for your reply. I got error 1004 error in Application defined or object defined. I am not sure if i have to dim anything ? or I miss out something? I am new and do not understand the code much. Please help.
 
Upvote 0
Hi Zax,

Thank you so much for your reply. I got error 1004 error in Application defined or object defined. I am not sure if i have to dim anything ? or I miss out something? I am new and do not understand the code much. Please help.
I assure to you that the code works perfectly,But you gotta check the workbooks names,are they the same names of your workbooks?????? ;)
ZAX
 
Upvote 0
Hi Zax,

Yes I understand now, I should put the workbook name as "workbook1.xlsx" full name. Then it works great now. THank you so much. Sorry for my stupid. I am very new with the vba. ;)
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top