2.19 - INDEX and MATCH Functions in Another Sheet with VBA
HTML-код
- Опубликовано: 22 июл 2024
- This video was part of a full Excel VBA course "Excel VBA Exercises and Real-World Projects" which was initially uploaded on Udemy in 2018/2019.
I have since decided to upload the course on RUclips so everyone can watch the content for free.
Excellent work. I am grateful. With a few modifications it works perfectly when doing index and match on other workbooks. Thank you very much indeed.
Thank you, Dimitri.
Big thanks for going thru this and explaining it very clearly - this worked awesomely well :)
Thanks Richard, I'm glad you found this video useful :)
Dude, best explanation on RUclips. Thank you!
Thanks, Wayne :)
This is what I've been searching for. Thanks man!
you Welcome, Charles :)
Thank you very much Karen. I have been searching for this code for weeks!
Thanks, Alex!
Thank you for the wonderful video!
You're welcome, Amy.
I can't seem to get the Index and Match to stick in my head. Your video saves the day every time.
Thank you, Stephanie...if you use Excel 365 I guess Xlookup is going to be easier than Index and Match :)
Thank You Very much
It is the best explanation on RUclips. Thank you for you work! But in case if you need to populate function further on additional columns what should I do?
Thanks for the nice words. Could you clarify your question, I'm not sure I understand it? If you mean that you need to populate the function horizontally, you should adapt your loop and cell references within it in accordance with your needs.
Thank you very much for your help, I have a doubt, in the part where you place offset(0,1) to which part of the sheet you refer please.
What happens is that I have tried to do it but it is that part I miss
Hi Roberto. Offset(0,1) means the column on the right....hope that helps
I love the video and learned some great tips. I am about to buy your two packages on your site. I need to do the exact same thing for spreadsheet at work, but it is not working. Pretty much I am looking to bring data from the sheet, maestroD, column "O", to masterD sheet column AM as long as the data from column W of maserD is matching the column G of maestroD. The way it is written below, it executes without an error, but no data populates in the column AM or masterD. Do you follow the script below and see an error?
Sub StaffingActual()
''Dim Variables
Dim i, j, n, t, x, y As Integer
Dim maestroD As Worksheet
Dim masterD As Worksheet
Dim matchrng As Range
Dim indexrng As Range
Set maestroD = ThisWorkbook.Worksheets("Maestro")
Set masterD = ThisWorkbook.Worksheets("Master Data")
' Without Moving the cursor
Application.ScreenUpdating = False
' Set n as the last row for Maestro data
n = maestroD.Range("A" & Rows.Count).End(xlUp).Row
' Set t as the last row for Maestro data
t = masterD.Range("A" & Rows.Count).End(xlUp).Row
' set match range
Set matchrng = maestroD.Range("g2:g" & n)
' set index range
Set indexrng = maestroD.Range("o2:o" & n)
For x = 2 To t
On Error Resume Next
masterD.Range("AM" & t).Value = Application.WorksheetFunction.Index( _
indexrng, Application.WorksheetFunction.Match( _
masterD.Range("w" & t).Value, matchrng, 0))
Next x
End Sub
Hi Laghmani,
I need to see the specific data set to be able to assist but from the code you pasted, I see the following issue: in your loop I believe you should change variable t with x in the following line, i.e.
masterD.Range("AM" & x).Value = Application.WorksheetFunction.Index( _
indexrng, Application.WorksheetFunction.Match( _
masterD.Range("w" & x).Value, matchrng, 0))
I assume in column A of both sheets you have the same number of rows as in the other columns, is that correct? So test this and let me know if it produces the result you expect.
Also, when you write a statement like this below, only the last variable y is of data type integer, the rest are of Variant.
Dim i, j, n, t, x, y As Integer
If you want all of them to be integers, it should be dim i as integer, j as integer and so on.
By the way, how large is your data set in terms of rows. If it's more than 32767 rows your code will result in a run-time error as the last row number will not be able to be stored in integer data type, in such case I recommend you use Long instead of Integer.
And finally, when you disable the screenupdating, don't forget to enable it before the end of the sub.
@@KarenTateosyan You are a genius!! That was the issue.
i have exactly the same problem
Nice video, everything is well explained! I realize this is not the place for support but i do have a question that maybe somebody can give me some tips for?
I used the SAME code as in the video and only changed the names to match the names of my worksheets and the columns but i get "runtime error 9 subscript out of range" on the lines where we set the worksheets "Set goalsWS =.... Set dataWs =....."
Do you know why i might be getting this error and where i should look to find a fix?
Never mind i solved it myself. In case anyone else gets this, the problem was that i created the VBA module in a different project. For this code to work the module needs to be in the project that contains the workbook that you are using, in other words it should me saved with the workbook.
@KarenTateosyan Do you happen to have any videos showing how this same code can be achieved by creating a module that is not in a specific workbook and doesn't need to be saved within it (so it can be used again if i happen to download the same workbook/worksheet but with updated/added contents and i need to repeat the same procedure).
Also do you have a video showing the same thing but the data being in 2 different workbooks instead of 2 different worksheets?
I am a bit overwhelmed by the amount of videos you have on your channel so i would appreciate it if you can recommend any of your videos that might help me with the above.
Hi, you can use VBA to open another Excel workbook, do what you need to do and then close it. You will have to adjust the code in accordance to your needs. Sorry for the slow reply.
Perfect
Thank you :)
Dear sir,
I want to know how to solution the three criteria index match function in Visual basic excel file ? Can i shear a excel file this related problem.
I create same code and worksheet as video, when I run but show Compile error. Can help?
hi Mark, may you post your code here to see what the issue might be?
@@KarenTateosyan Hi Karen, I already found and correct due my spelling mistake, I run your code to get the result as I aspected. Your code is very help in my work.
@@KarenTateosyan Wanna ask if you also prepare sample “Sumproduct Match Index” function video? Thx
Thanks for the suggestion, Mark...I hope to find time soon to post more videos on my channel :)
@@KarenTateosyan thx, see your next video soon
Very nice explanation but I tried several times and although there is no error, the code doesn't return any value
hi Claudio, could you paste your code to see what might be the issue. Also - did you use similar or differene data set?
Thanks for the video! How I can use 2 conditions for MATCH? I tried below, but it gives me some number I don't know where it takes it from.
goalsws.Range("G" & 7).Value = (Application.WorksheetFunction.Index( _
IndexRng, _
Application.WorksheetFunction.Match(goalsws.Range("O" & 2).Value, MatchRng, 0) _
+
_
Application.WorksheetFunction.Match(goalsws.Range("O" & 3).Value, MatchRng2, 0)))
Hi Eldar and sorry for the delay in reply, for some reason I didn't receive a notification regarding your comment.
As for your question, in your code I see a varialbe MatchRng2 - have you declared / set this before using it?
Hi Karen! Thanks for the response. Yes I declare MatchRng2 the same way as I did with the MatchRng, however the code works only with one condition and when I try 2 conditions I get an error. Unfortunately this week I'm out pf office and can't tell the exact error.
Should my code work though? If it should work the way I wrote it, I will recheck everything first thing I return to the office.
Hi Eldar,
Ideally I will have to check your entire code (and also the data set you work with) to be able to check it. But generally, as a syntax it should look like what you have pasted before.
regards
@@eldar743 ruclips.net/video/D5H5T-uSS7o/видео.html
HHGg mi j o hi hup
?