Match from closed workbook
-
- PlatinumLounger
- Posts: 4936
- Joined: 31 Aug 2016, 09:02
Match from closed workbook
Hello everyone
In fact this issue seems easy but I tried and failed to achieve it .. I have two workbooks "Main" where I need to put the expected results in column F by looking up column A and the closed workbook "ClosedWB" the data are in columns D & E
If there is a faster way to do such task I would appreciate that a lot using ADO for example
Thanks advanced for great help
In fact this issue seems easy but I tried and failed to achieve it .. I have two workbooks "Main" where I need to put the expected results in column F by looking up column A and the closed workbook "ClosedWB" the data are in columns D & E
If there is a faster way to do such task I would appreciate that a lot using ADO for example
Thanks advanced for great help
You do not have the required permissions to view the files attached to this post.
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Match from closed workbook
You obviously want to avoid VLOOKUP's and Power Query?
Are you needing a macro that runs a query statement?
Is there any reason for avoiding the other scenarios?
Are you needing a macro that runs a query statement?
Is there any reason for avoiding the other scenarios?
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- PlatinumLounger
- Posts: 4936
- Joined: 31 Aug 2016, 09:02
Re: Match from closed workbook
Thanks a lot for reply Mr. Rudi
In fact I am searching all the time for the best method and faster vba codes .. so if it is found a better way I appreciate that a lot. But if it is only one way no problem of that way in that case
In fact I am searching all the time for the best method and faster vba codes .. so if it is found a better way I appreciate that a lot. But if it is only one way no problem of that way in that case
-
- Administrator
- Posts: 78901
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Match from closed workbook
Open both workbooks.
In F2 in Sheet1 of Main.xlsm, enter the formula
=IFERROR(VLOOKUP(A2,[ClosedWB.xlsm]Sheet1!$D:$E,2,FALSE),"Not Found")
Fill down as far as needed.
Now close ClosedWB.xlsm. The formulas will still work!
In F2 in Sheet1 of Main.xlsm, enter the formula
=IFERROR(VLOOKUP(A2,[ClosedWB.xlsm]Sheet1!$D:$E,2,FALSE),"Not Found")
Fill down as far as needed.
Now close ClosedWB.xlsm. The formulas will still work!
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4936
- Joined: 31 Aug 2016, 09:02
Re: Match from closed workbook
Thanks a lot Mr. Hans
I have this code and it works except for "Not Found" part
I have this code and it works except for "Not Found" part
Code: Select all
Sub VLOOKUP_Closed_Workbook()
Dim wbk As Workbook
Dim ws As Worksheet
Dim sh As Worksheet
Dim x As Variant
Dim str As String
Dim i As Integer
Dim sFolder As String
Dim fName As String
Application.ScreenUpdating = False
Set ws = ThisWorkbook.Worksheets("Sheet1")
sFolder = ThisWorkbook.Path & "\"
fName = Dir(sFolder & "ClosedWB.xlsm")
Set wbk = Workbooks.Open(sFolder & fName)
Set sh = wbk.Worksheets("Sheet1")
For i = 2 To ws.Cells(Rows.Count, 1).End(xlUp).Row
x = ""
str = ws.Cells(i, 1).Value
On Error Resume Next
x = Application.WorksheetFunction.VLookup(str, sh.Range("D2:E" & sh.Cells(Rows.Count, 4).End(xlUp).Row), 2, False)
If IsEmpty(x) Or IsError(x) Or x = "" Then ws.Cells(i, 6) = "Not Found"
On Error GoTo 0
ws.Cells(i, 6) = x
Next i
wbk.Close False
Application.ScreenUpdating = True
End Sub
-
- PlatinumLounger
- Posts: 4936
- Joined: 31 Aug 2016, 09:02
Re: Match from closed workbook
After seeing your formula I used it in the code
Thanks a lot for great help
In your opinion is there a faster way for that or that is considered the faster way?
Code: Select all
With ws.Range("F2:F" & ws.Cells(Rows.Count, 1).End(xlUp).Row)
.Formula = "=IFERROR(VLOOKUP(A2,[ClosedWB.xlsm]Sheet1!$D:$E,2,FALSE),""Not Found"")"
.Value = .Value
End With
In your opinion is there a faster way for that or that is considered the faster way?
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Match from closed workbook
I was about to suggest this (like Hans)
=IFERROR(VLOOKUP(A2,[ClosedWB.xlsm]Sheet1!$D$1:$E$6,2,0),"Not Found")
Why use code? A formula will be easier to maintain and probably more optimal (unless there is a reason for VBA)?
=IFERROR(VLOOKUP(A2,[ClosedWB.xlsm]Sheet1!$D$1:$E$6,2,0),"Not Found")
Why use code? A formula will be easier to maintain and probably more optimal (unless there is a reason for VBA)?
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78901
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Match from closed workbook
If you want to use code, setting the formula and then replacing the formulas with their values is probably the most efficient way.
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4936
- Joined: 31 Aug 2016, 09:02
Re: Match from closed workbook
Thanks a lot .. This is a part of huge project that rely on VBA ..so I don'u use formulas in that case
-
- PlatinumLounger
- Posts: 4936
- Joined: 31 Aug 2016, 09:02
Re: Match from closed workbook
Thanks Mr. Hans
I already took your advice as for setting the formula and then replacing the formulas with their values
May you have a look and see if there is something wrong with it now .. And if there is any point please tell me about it
Best Regards for both of you
I already took your advice as for setting the formula and then replacing the formulas with their values
May you have a look and see if there is something wrong with it now .. And if there is any point please tell me about it
Best Regards for both of you
-
- Administrator
- Posts: 78901
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Match from closed workbook
As you would have found out if you had tried to create the formula yourself, you have to enclose the workbook/worksheet name in single quotes if either contains spaces:
Substitute the actual names.
Code: Select all
.Formula = "=IFERROR(VLOOKUP(A2,'[Closed WB.xlsm]Sheet 1'!$D:$E,2,FALSE),""Not Found"")"
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4936
- Joined: 31 Aug 2016, 09:02
Re: Match from closed workbook
Thanks a lot for that
What if the workbook is not in the same path .. how can I fix that?
What if the workbook is not in the same path .. how can I fix that?
-
- Administrator
- Posts: 78901
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Match from closed workbook
Change
Code: Select all
sFolder = ThisWorkbook.Path & "\"
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4936
- Joined: 31 Aug 2016, 09:02
Re: Match from closed workbook
I don't mean that part Mr. Hans
I mean how to fix the formula to suit the code
I mean how to fix the formula to suit the code
-
- Administrator
- Posts: 78901
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Match from closed workbook
There is no need to change the formula if the other workbook is open. In that situation, you don't have to specify the path, only the workbook name.
If you want to create the formula while the workbook is closed, you do have to include the path:
Substitute the actual path, workbook name and worksheet name.
If you want to create the formula while the workbook is closed, you do have to include the path:
Code: Select all
.Formula = "=IFERROR(VLOOKUP(A2,'C:\MyFolder\MySubFolder\[Closed WB.xlsm]Sheet 1'!$D:$E,2,FALSE),""Not Found"")"
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4936
- Joined: 31 Aug 2016, 09:02
Re: Match from closed workbook
Thank you very very much Mr. Hans
Best and Kind regards
Have a nice time
Best and Kind regards
Have a nice time
-
- PlatinumLounger
- Posts: 4936
- Joined: 31 Aug 2016, 09:02
Re: Match from closed workbook
Hello again
I have an issue in that thread ..
I need to lookup in two closed workbooks not only one as shown earlier. The scenario would be to search the first closed workbook with name "ClosedWB" and if it is found to put the result .. But if it is not found then to search another closed workbook named "ClosedWB2" and if found that is ok and the result would be put .. Finally if the search in the two closed workbooks have no results then type "Not Found"
Thanks a lot for great help
I have an issue in that thread ..
I need to lookup in two closed workbooks not only one as shown earlier. The scenario would be to search the first closed workbook with name "ClosedWB" and if it is found to put the result .. But if it is not found then to search another closed workbook named "ClosedWB2" and if found that is ok and the result would be put .. Finally if the search in the two closed workbooks have no results then type "Not Found"
Thanks a lot for great help
-
- Administrator
- Posts: 78901
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Match from closed workbook
Something like
Code: Select all
.Formula = "=IFERROR(VLOOKUP(A2,'C:\MyFolder\MySubFolder\[Closed WB1.xlsm]Sheet 1'!$D:$E,2,FALSE),IFERROR(VLOOKUP(A2,'C:\MyFolder\MySubFolder\[Closed WB1.xlsm]Sheet 1'!$D:$E,2,FALSE) ,IFERROR(VLOOKUP(A2,'C:\MyFolder\MySubFolder\[Closed WB2.xlsm]Sheet 1'!$D:$E,2,FALSE),"Not Found"") )"
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4936
- Joined: 31 Aug 2016, 09:02
Re: Match from closed workbook
That's awesome. Thanks a lot Mr. Hans
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Re: Match from closed workbook
Hello, a slightly late Merry New Year everyone :-)
Here is a very simple way that I use to get the tricky syntax correct for a closed workbook reference..
So, as example:
Say I have a Workbook, Workbook1.xlsm , where I need a closed reference to some range in a closed Workbook, Workbook2.xlsm ( As example, I chose arbitrarily, E3:E5 in the second worksheet of Workbook2 )
_A) Open both Workbooks:
_B) Select any spare cell ( I use arbitrarily C2 here ) in Workbook1, and type a _....
=
_....in it
Put a Equal sign in cell C2.jpg : https://imgur.com/lkCD2p7" onclick="window.open(this.href);return false;
_C) Select the required range in Workbook2. ( I chose as example, arbitrarily, E3:E5 here )
Select range In Wb2.jpg : https://imgur.com/dQKTjl6" onclick="window.open(this.href);return false;
_D) Hit Enter.
You should see something taking this sort of a form up in the Formula Bar in Workbook1:
=[Workbook2.xlsm]Shite2!$E$3:$E$5
OpnRefUpInFxBarWb1.jpg : https://imgur.com/T3CfRJV" onclick="window.open(this.href);return false;
_E) Close Workbook2
You should see that the formula in the formula bar changes to a closed reference type form, something like this:
='G:\Excel0202015Jan2016\OffenFragensForums\RCA1JorgeAllenJan\[Workbook2.xlsm]Shite2'!$E$3:$E$5
ClsdRefUpInFxBarWb1.jpg : https://imgur.com/9FiVYE6" onclick="window.open(this.href);return false;
This final formula is that which you require for a closed reference placed in Workbook1 and referring to the range E3:E5 in Workbook2
_F) Copy that formula, paste it in where you need it, and finally delete the content in Cell C2 in Workbook1
Alan
_._______________________________--
P.s.
Just out of interest:
Lets say that you wanted that closed reference across some range such as B2:B4 in the first Worksheet of Workbook1
You could do that by pasting a single formula into that range. The used single formula would need to be modified such that is was in the fixed vector relative referencing form, ( in other words, change $E$3:$E$5 to E3 )
( By virtue of using the fixed vector relative referencing form, the actual “seen” formulas are adjusted accordingly to reflect the relative offset position of where they are
= ……… …[Workbook2.xlsm]Shite2'!E3
= ……… …[Workbook2.xlsm]Shite2'!E4
= ……… …[Workbook2.xlsm]Shite2'!E5
I have often wondered if using that single formula “fixed vector relative referencing form” might have any advantages over this line , which one most usually sees, and which, at first glance does the same
Let Worksheets.Item(1).Range("B2:B4").FormulaArray …………\[Workbook2.xlsm]Shite2'!$E$3:$E$5"
Ref
http://www.eileenslounge.com/viewtopic.php?f=30&t=25213" onclick="window.open(this.href);return false;
http://www.eileenslounge.com/viewtopic. ... 20#p197707" onclick="window.open(this.href);return false;
https://www.excelforum.com/development- ... ost4608252" onclick="window.open(this.href);return false;
https://www.excelforum.com/development- ... ost4618975" onclick="window.open(this.href);return false;
https://www.excelforum.com/excel-progra ... ost4813760" onclick="window.open(this.href);return false;
https://www.excelforum.com/excel-progra ... ost4321006" onclick="window.open(this.href);return false;
https://exceloffthegrid.com/getting-val ... -workbook/" onclick="window.open(this.href);return false;
Here is a very simple way that I use to get the tricky syntax correct for a closed workbook reference..
So, as example:
Say I have a Workbook, Workbook1.xlsm , where I need a closed reference to some range in a closed Workbook, Workbook2.xlsm ( As example, I chose arbitrarily, E3:E5 in the second worksheet of Workbook2 )
_A) Open both Workbooks:
_B) Select any spare cell ( I use arbitrarily C2 here ) in Workbook1, and type a _....
=
_....in it
Put a Equal sign in cell C2.jpg : https://imgur.com/lkCD2p7" onclick="window.open(this.href);return false;
_C) Select the required range in Workbook2. ( I chose as example, arbitrarily, E3:E5 here )
Select range In Wb2.jpg : https://imgur.com/dQKTjl6" onclick="window.open(this.href);return false;
_D) Hit Enter.
You should see something taking this sort of a form up in the Formula Bar in Workbook1:
=[Workbook2.xlsm]Shite2!$E$3:$E$5
OpnRefUpInFxBarWb1.jpg : https://imgur.com/T3CfRJV" onclick="window.open(this.href);return false;
_E) Close Workbook2
You should see that the formula in the formula bar changes to a closed reference type form, something like this:
='G:\Excel0202015Jan2016\OffenFragensForums\RCA1JorgeAllenJan\[Workbook2.xlsm]Shite2'!$E$3:$E$5
ClsdRefUpInFxBarWb1.jpg : https://imgur.com/9FiVYE6" onclick="window.open(this.href);return false;
This final formula is that which you require for a closed reference placed in Workbook1 and referring to the range E3:E5 in Workbook2
_F) Copy that formula, paste it in where you need it, and finally delete the content in Cell C2 in Workbook1
Alan
_._______________________________--
P.s.
Just out of interest:
Lets say that you wanted that closed reference across some range such as B2:B4 in the first Worksheet of Workbook1
You could do that by pasting a single formula into that range. The used single formula would need to be modified such that is was in the fixed vector relative referencing form, ( in other words, change $E$3:$E$5 to E3 )
Code: Select all
Sub PutMyTestEIn()
Let Worksheets.Item(1).Range("B2:B4").Value = "=" & "'G:\Excel0202015Jan2016\OffenFragensForums\RCA1JorgeAllenJan\[Workbook2.xlsm]Shite2'!E3"
End Sub
= ……… …[Workbook2.xlsm]Shite2'!E3
= ……… …[Workbook2.xlsm]Shite2'!E4
= ……… …[Workbook2.xlsm]Shite2'!E5
I have often wondered if using that single formula “fixed vector relative referencing form” might have any advantages over this line , which one most usually sees, and which, at first glance does the same
Let Worksheets.Item(1).Range("B2:B4").FormulaArray …………\[Workbook2.xlsm]Shite2'!$E$3:$E$5"
Ref
http://www.eileenslounge.com/viewtopic.php?f=30&t=25213" onclick="window.open(this.href);return false;
http://www.eileenslounge.com/viewtopic. ... 20#p197707" onclick="window.open(this.href);return false;
https://www.excelforum.com/development- ... ost4608252" onclick="window.open(this.href);return false;
https://www.excelforum.com/development- ... ost4618975" onclick="window.open(this.href);return false;
https://www.excelforum.com/excel-progra ... ost4813760" onclick="window.open(this.href);return false;
https://www.excelforum.com/excel-progra ... ost4321006" onclick="window.open(this.href);return false;
https://exceloffthegrid.com/getting-val ... -workbook/" onclick="window.open(this.href);return false;
Last edited by Doc.AElstein on 18 Dec 2018, 11:18, edited 2 times in total.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also
You can find me at DocAElstein also