Hello,
I have two Excel workbooks, BOOK A contains a master list of ID Numbers and corresponding address information. BOOK B has ID Numbers but no address information. I am looking for a VBA that will use a list of ID Numbers from BOOK B, find them in BOOK A, copy the address information from cells B,C,D,&E in Book A, and drop them next to the ID Number from the list in Book B. Any suggestions?
Re: VBA that uses a list to pull data from another sheet.
Hello,
why not an easy Excel basics worksheet VLOOKUP formula rather than any VBA procedure ?
Thanks for the response, I was under the assumption that VLOOKUP was only for data contained in a single workbook. My data sets live in two workbooks. Please correct me if I am wrong, my advanced Excel skills are minimal.
Re: VBA that uses a list to pull data from another sheet.
As the Excel basics worksheet function VLOOKUP works whatever the workbook, even closed ! …
That's good to know, any idea what the formula would look like based on the attachments? I'm having trouble wrapping my head around what to key in.
.
Hello LIBRARYBEND.
- If the number of data rows in A and B is small, then using VLookUp is enough for you.
- If that amount is a bit larger, then it's more efficient to use the pair: Index and Match.
- And if the number of rows of data is very large, a good macro would be suitable.
So I ask:
- How many rows of data do these workbooks have?
- When you go to do this search for information: Will workbook A be closed or will both workbooks be open?
Saved both workbooks as macro enabled (.xlsm) workbooks.
Both workbooks need to be open. If not, let us know which workbook will have the macro in it so code can be changed for that.
Change references where required.
As it stands, code can be run from either workbook.
Please Login or Register to view this content.
The inherent weakness of the liberal society: a too rosy view of humanity.
Or try this ...
In case BOOK A - whatever opened or not - in same path as BOOK B already opened
so according to the attachment an Excel beginner formula VBA demonstration - can be located in a third workbook - for starters :
PHP Code:Sub Demo1()
Const C = "'[BOOK B.xlsx]Sheet1'!B2"
V = Evaluate("ISREF(" & C & ")"): If IsError(V) Then V = False
If V Then
With Range(C & ":E" & Range(C).CurrentRegion.Rows.Count)
.Formula = Evaluate("""=IFERROR(VLOOKUP(FIXED(A2,0,TRUE),'" & .Parent.Parent.Path & _
"\[BOOK A.xlsx]Sheet1'!A:E,""&{2,3,4,5}&"",FALSE),"""""""")""")
.Formula = .Value
End With
End If
End Sub
► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄
Last edited by Marc L; 11-05-2022 at 08:58 AM. Reason: optimization ...
Hello. In this variant:
- Book A can be open or closed.
- Book B will contain the macro.
PHP Code:Sub Test1()
Dim Conn$, Rst, Cel As Range
Conn = "Data Source=" & ThisWorkbook.Path & "\Book A.xlsx;Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0; HDR=NO'"
Set Rst = CreateObject("ADODB.Recordset"): [a1].CurrentRegion.Offset(1, 1).ClearContents
For Each Cel In Range("A1", Cells(Rows.Count, 1).End(xlUp))
Rst.Open "Select F2,F3,F4,F5 FROM [Sheet1$A1:E] Where F1='" & Cel & "'", Conn, 3, 3, 1
If Not Rst.EOF Then Cel(, 2).CopyFromRecordset Rst
Rst.Close
Next
End Sub
Re: VBA that uses a list to pull data from another sheet.
Originally Posted by beyond Excel
As a reminder Book A should be closed to avoid the well known 'data leak' issue with ADODB, rare but may occur …
Known 'data leakage' issue with ADODB?...
Not so well known: What do you mean, Mark?
Re: VBA that uses a list to pull data from another sheet.
Such issue may occur when a workbook is opened under Excel and reached via ADODB under a VBA procedure
so the reason why it's better recommanded to use ADODB only on closed workbooks …
Mmm...
Such a "thing" never happened to me: And I hope that in the future it never happens to me!
Although I did not understand what is the problem that occurs sporadically ...
Re: VBA that uses a list to pull data from another sheet.
It's rare indeed. But keep it in mind if one day you face this issue as it is difficult to catch …
I have a "slight" suspicion that this can happen in the case of writing information and not in the case of reading information, like this query: But we'll see!
Thanks for the comment.
Memory leak by ADO connection with open workbook is well known issue.
LIBRARYBEND,
I prefer VLookUp formula, it is easy and fast.
If ADO connection
Assuming both workbooks are in the same folder.
Please Login or Register to view this content.
Last edited by jindon; 11-08-2022 at 02:30 AM. Reason: One line added
Hey Jindon: You're doing a bit of "cheating"! (LOL)
In the original workbook B, the information in column A are numbers that are left-aligned, while in workbook B that you loaded in your recent proposal, they are numbers but saved as text.
And that's why the proposal works for you. Otherwise, the following would not work in ADODB: B.`Par ID` = A.`Par ID`, right?...
If you call it "cheating", it is your idea, so I uploaded the file.
Mind yours, rather than picking up a tiny dust from other ones code.
.
- The workbook is in post #1.
- And I didn't understand your second comment: I have a feeling that you hasn't been very kind and it doesn't correspond to my technical observation, but... There you are!
Why people as such can only comment from what they guess...
This worked brilliantly, thanks for your help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks