Learn Excel - Video 237 - VBA Vlookup Function with loops
HTML-код
- Опубликовано: 15 окт 2024
- In this video we are learning how to use Vlookup in VBA and combine it with loop and error handlers.
Please see the below series information along-with links and join me in these world -class designed live classroom sessions. Bet. you will not be disappointed.
POWERPIVOT WITH DAX - PART1/2 (New Launched)
www.udemy.com/...
POWERPIVOT WITH DAX - PART2/2 (Just Launched)
www.udemy.com/...
LEARN DAX IN POWERBI DESKTOP - Part 1/2
www.udemy.com/...
LEARN DAX IN POWERBI DESKTOP - Part 2/2
www.udemy.com/...
POWER BI -BASIC TO ADVANCE PART 1 / 2 (New Course)
www.udemy.com/...
POWER BI -BASIC TO ADVANCE PART 2 / 2 (New Course)
www.udemy.com/...
Excel Power Query
www.udemy.com/...
Excel Power Query M Code Programming - Part 1
www.udemy.com/...
Excel Power Query M Code Programming - Part 2
www.udemy.com/...
Excel Power Query M Code Programming - Part 3
www.udemy.com/...
• [Excel VBA Series Links are mentioned below - basic to super advance]
Excel VBA Introduction Series 1
www.udemy.com/...
Excel VBA Loops and IF functions Sereis 2
www.udemy.com/...
Excel VBA Collection Loops and Workbook /Sheets handling Series 3
www.udemy.com/...
Excel VBA Super Strong Arrays Series 4
www.udemy.com/...
Excel VBA Functions Series 5
www.udemy.com/...
Excel VBA File and Folder Handling Series 6
www.udemy.com/...
Excel VBA In depth UserForm Series 7
www.udemy.com/...
Excel VBA error handler and Message box /input Box Series 8
www.udemy.com/...
Excel VBA Amazing and Phenomenal Events Series 9
www.udemy.com/...
Excel VBA Awesome Pivots - Series 10
www.udemy.com/...
Excel VBA Charts Series 11
www.udemy.com/...
Excel VBA Connecting Outlook Series 12
www.udemy.com/...
Excel VBA Connecting Powerpoint Series 13
www.udemy.com/...
Excel VBA Connecting MS Access - Series 14
www.udemy.com/...
Excel & Access VBA Dashboard and Projects - Series 15
www.udemy.com/...
Web Scraping Using Excel VBA - Series 16
www.udemy.com/...
VBA Web Selenium Web Scraping Part 1/2
www.udemy.com/...
VBA Web Selenium Web Scraping Part 2/2
www.udemy.com/...
• [MS Access VBA Series]
Access VBA Introduction Series 1
www.udemy.com/...
Access VBA Arrays and Functions Series 2
www.udemy.com/...
Access Userform and SQL Series 3
www.udemy.com/...
Access VBA File Handling and Error Handlers Series4
www.udemy.com/...
Connection Set-Up AccessVBA & Security (ADODB &DAO Series-5)
www.udemy.com/...
Connect XL & Outlook Access VBA & Collection Loops Series 7
www.udemy.com/...
wow - you were so clear and even with zero knowledge we can learn
Thank you
Thanks Ajay it will more helpful if you make video with iferror + vlookup formula by vba code.
Wow that's fantastic.i tried same yesterday but same errors i was faced..i watched another video s regarding that.but simply u are the best..as it is very very good.
No words for your teaching skills....You are the best of all I seen till now 🙏
Thank you for your great words
Sir sir sir.. you are great...
Your teaching style is very friendly and simple..
Thank you 😊 sir
Keep watching
This what i wanted..!! I have been searching for this for 3 day. But now today i got the correct and easy way to do this.
Thanx bro..!!👍👍
Good good... Keep it up
Very simple and understandabe way as you mentioned, great job thanks
very excellent style of teaching. Every concept of VBA is very clear through your Videos. In every video we learn some new concepts of VBA which i never learn before.
Welcome to the channel..
Thanks a lot for this! Been picking my hair the last 2 hours on Stackoverflow to find the solution but nothing comes close to this. Very simple code and great explanation :)
Thank you. Very happy to know you got it
Very very useful video nicely explained
Amazing code ... Helped a lot ... Explained beautifully
Very informative and helful.. The steps are explained very well
Very nice and really helpful 😊 your explanation is awesome
Thank you
Its very nice but if i have to apply more vlookup in next column for any other value in that case what should i do?
Hi Ajay, I have seen many videos on vlookup. They are so complex to understand but your code is so simple to understand. It is nice & your way of defining is superb.
Can you please define how I can select dynamic range of data in above code. In my case I import my sales data every day and the range is dynamic. Hope you clarify soon.
Thanks
See rest vkookp videos under Playlist excel vba functions.
Super vedio and super trick for taking range -3 👌
Happy you liked it
Thank you very much for this great video and for this valuable informations and for this simple and clear and wonderful method.
Hi Ajay, Good afternoon
Hope you are doing well!!
My query is in excel sheet if will apply the Vlookup function as soon as we enter the lookup value formula will bring the value from table.
for VBA I need to click the command botton then only the value will show. Is there any feasibility to do as we are getting with formula
Greater than the greatest.
Grand Salute to you Sir!!
Thanx sir.
Nice video.
I am using vlookup function on A sheet having 50000 rows nd 46 column with an unique I'd.and correspondence workbook have more header with 200000 rows. Vlookup just looking unique I'd and pasting the data accordingly. My code is going undefined stage. How much time it should be take to finish. I have used your code with do while loop. It's working nice with F8.
U r A perfect teacher . I wish u A suessful and bright future.
More than great
One question, if we do multiple filter in column of look-up value, and apply the same vba code will it bring only the selection of filter value results?
Hi Ajay,
As explained the error function applied in loop concept, but not working. Can you please explain.
And also We are decrypting 100's of Excel files using vba as common password as "1". Some times others giving another password, then how can we find out that file name without debugging the procees?
Please help in this.
Hi sir......one query hai meri....mere pass ek data means master file hai usme name, mobile no, city, order, pincode,status - Dispatch & Pending hai. muje master file me se Dispatch & Pending file new sheet me Lana hai.dispatch & pending data separate mean new sheet create honi chahiye. Pending & dispatch 2 new sheet create hone ke baad jab measter file me new data add Karu tab wo data status ke jariye pending & dispatch sheet me add hona chahiye automatically....ye query formula or VBA coding ke kaise solve hogi...plz tell me formula or VBA code.
2 साल पहले का वीडियो 2 साल बाद देख रहा हूं दुर्भाग्य है मेरा ।
My dear student.. Never say so. Your time has come. Now shine... Ab dekh Lau sabhi videos. Life mein learning jab bhi start ho tabhi badia
@@AjayKumarparmar आपके शीघ्र उत्तर के लिए धन्यवाद । क्योंकि मैं आपका विद्यार्थी हूँ तो मन की झिज्ञासा को आपके समक्ष रखना चाहता हूं । मैंने एक userform बनाया है जिसमे Sheet1 के A:A सेल में मैटेरियल्स लिस्टेड हैं । इसे मैं फ़िल्टर करना चाहता हूं । मैं चाहता कि combobox1 में जब मैं
मटेरियल सर्च करू तो , dropdown में मुझे suggestion दिखाई दे । Ex- जब में combobox में ind लिखूं तो dropdown में india और west indies दोनों show होना चाहिए । और जब कोई दूसरा word टाइप करूं तो पहला null होना चाहिए और searchbox में टाइप word के हिसाब से दूसरे सिमिलर डेटा दिखाना चाहिए । तो गुरुजी जल्दी बनाइये ऐसा कुछ लॉजिकल userfrom
Nice video. Just have a question. When I have multiple columns as my range for vlookup, how do I provide it. For e.g. if my range is from A3 to BX and the number of rows are huge, say 50000 rows, can I refer the range in vlookup something as follows, Vlookup (Tax-id, Range ("A3:BX" &rowcount), 24,FALSE). Here, the rowcount is the total number of rows available in my table that needs to be referred. I. getting an error application defined or object defined error. Can you please guide. I am relatively new to Excel VBA/Macros. Thanks.
very useful nd very good vba vlookup with for loop but in this example only use one column but if we have to use more then use column then what we will do sir but how cn we use invba vlookup with match for column plz if possible then plz share with me this topic
Wish you all the best ...
Amazing, Clear, Informative.
You are such good tutor. Thanks a lot to you.
Thanks Ajay Sir,
it's an great Video ever on Vlookup
I am really, really enjoying it & it was a grt tip to change variable I from to IThank you so much... Please don't ever stop ever uploading videos what may come.
As long as viewers are watching videos and ads...they giving me great support... thank you
Really great man... After watching so many video, u clarified my doubts 👏
I wan to ask what if my data base row are variable
Can i use the below code
H = Application.WorksheetFunction.CountA(Worksheets("SQL Results").Range("$Y:$Y"))
Range("W" & i + 1).Value = WorksheetFunction.Vlookup(Range("B" & i + 1).Value, Range("Y" & C), 1, False)
Do i need to put $ signs to freeze the table
Sir hum userform ko fully dynamic kaise create kare excel mein. Pls pls reply...
awesome it very good easy way to use through VBA.
thanks Rajeev..
Hi Ajay,
I wanted to know how can I use the same Vlookup formula in 2 different workbooks using Macros
Use inbuilt vlookup VBA function with collection loops
I have a doubt
If I want the value as yes or no.. how can I modify the code?
it helps me in my macro. Thanks a lot!
I have a question and sample Excel file. how do I reach out to you?
somehow I will try to explain my doubt.
How to copy the pivot table to a different sheet (I can do this) and use the match function with a few conditions that are in the different sheets. This should be dynamic
You can email me for this project. Ajayk3802@gmail.com or what's app 9810375599
Amazing bro.. Amazing.... really I salute to you.... you are my best teacher of excel in world.....
Amazing brother,
great work Sir,, thumbs up from Pakistan,
where do we get this workbook for practice?
Great video, thanks.
You are welcome!
Hello sir, similarly what if there are multiple same look-up value and we need to get next values then how to modify this logic? Please let me know it's bit important for me to know
Better create VBA udf
Thank you Ajay this is very usefully to me
Great video Sir..
But one dought i have in my mind.
How I can use event for automatic changes in values. If I don't want button press option.
Great Bro
Sir, I am a beginner to vba code. For getting a job how much should I knw about vba as a fresher?
you can join my course. Check my website www.excelvbalover.com
What will be the add-on in code, if values should copied with cell format?
You can check cell Formatting using cell properties and implement same
Thank you so much . Such a wonderful video
Awesome....
Good Explanation Ajay Sir,
I have one query sir, I used the same VBA script for vlookup using loop and i also got the data however some data not reflected even if the vlookup data was available in the sheets
Playlist is Excel vba functions hindi. Go here and see more vlookup videos in hindi.. You will learn more and will be able to sort out. If you using on error resume next then vba will not give error so remove the line and debug again
Hello, I have a problem with an Excel worksheet (there are 3 that look the same) now it is that I want to look up, (it is quite complicated,) I first want to indicate the sheet where I want to search then the ID NAME ( which is at the top), then in the columns associated with the 1st ID, each ID has a name of the person, (20 people), each person has 5 columns, 1) the ID, 2) the name of a person who changed, 3, 4, 5 are data that I want to retrieve, now I don't know if I have to use a form or just make a separate timetable for each worksheet, can you help me? Feel free to ask if you have any questions.
for any project related thing you can visit my website www.excelvbalover.com or email me at ajayk3802@gmail.com
Awesome Sir.
Hi Ajay, can you please help me... How can I use Networkdays function in MS-Access
Same as excel
awesome
how can use vlookup with mulitple workbook if you have already make videos plz share link......thanks
yesterday i uploaded a video...check that...playlist name is excel vba functions
Hi Ajay, I am learning by Your videos. Its Amazing. Can you please give a link for Vlookup Function for Multiple columns selection at a time from Different Workbook to current workbook based on Ids..
Check excel vba function hindi Playlist
Awesome👍
Thanks 🤗
Thanks man, you're the best
thank you so much, it was amazing regards from México
love to mexico from india...
Hi Sir,
I have to two workbooks wb1 and wb2 now I want to copy the data from sheet1 of wb1 by searching the particular column by its "name" and then pasting in sheet1 of wb2 by again finding that particular column by its "name" then pasting the wb1 sheet1 data in sheet1 of wb2. I have multiple columns in the sheet1 of wb1. I want to loop through all columns that I want in sheet1 of wb1 and repeat above task for them. And While finding column it should find by column "name" and not by column number, since my sheets column position keeps changing.
Please help on this....!!
If I delete the table from which the data I want to add after applying the formula in the main table (result table) does the value in main table will be same or changes or it will show error
It works same way as in excel front end. Now depending on the situation we can modify the things. When macro runs it overwrite previous things. If you want to ignore there are several ways... First thing... You should understand that anything can be done in vba.. How one want...
Hi Ajay
Can you share the VBA code for median mode and SD for multiple criteria ......
It's criteria will be same like sumifs and count ifs function.....
U can use vba functions
@@AjayKumarparmar pls help to get the solution...if you share the video many user can know about how to used mean mode , median and SD on multiple criteria ........
@@manojsogam7763 you can learn functions and loops. Nothing else is required. Base needs to be strong
Thank you for your prompt response
Sir how to use this formula in multiple columns
HI I have a question would you be able to help me find an error message in my vlookup macro from another worksheet that it's not picking up the formula.
sir I want to make vlookup via arrays 10 to 12 columns can be possible that we can use only one loop and data should be update in all columns , so plz suggest how can I get all data via loop
check vlookup series - playlist name is excel vba functions hindi
Hay sir, if more than one that 1 range I want to look for please give an example, examp : I want yo get year too
Check more vlookup video on same
Sir,
Could you please make a video of mailmerge using vba? I know how to merge from excel to word. But i need the result like each word documents should be saved in seperate pdf files. Please help us.
How to create a daynamic dependent drop-down list based on 3 combo boxes in VBA without using any name range or Excel sheet entry.
Sir, userform me vlookup kaise find karenge, agar hame string k sath variable v find krna ho to
Just use correct data type
@@AjayKumarparmar I don't understand what you say, actually I just find out the numbers and alphabets too, in a same criteria. Actually I used "clng" for numbers but I don't know how to find the alphabets using vlookup.
@@AjayKumarparmar which data type sir, plz help
Hi Can this be done using xlookup?
Yeah
@@AjayKumarparmar please drop me the code thank you in advance
Sir... Please do a vlookup for filtered data video........
Hi that is too good, but I want to know is it possible to use vlookup with match function
Yes.. Check Playlist excel vba functions
Sir, Kudos to you! Amazing ! What if we want to leave the cell blank instead of not found , so we can = "" ?
Yes.. You can..
Sir thanks for such a nice explanation, i want to do something with other workbook, like i have three workbook with some data in all workbook with one unique id column which is common in all three workbook, so i want to pull two columns from one workbook and one column from 2nd workbook to the third workbook with that unique common column values using VBA, can you help me getting. It will be helpful for me. Thanks!!
Great video brother. Looking forward to watch the other videos. Thanks a lot.
Thanks shafiul. Please make other people aware of the channel. You call me brother so brother need more people come to channel and learn.rise and shine.
HI Ajay, Thanks for replying. I will definitely let my colleagues and friends know about your wonderful channel. Keep up the good work. Thanks again.
Hi Sir,
This video is awesome, but why don't you change the nameing convention of the video because it has vlookup, loops two type of error handling. This is really mind bowling video.
Thanks again for liking the work. Title Says it's awesome so I meant it when I said that...:)
Thank you so much . This video is really helpful😊
Thanks for the feedback
Wt if i want to pull data from other excel?. Wt will be change in code
Define workbook using set keyword.. and same for sheet and ranges
I teach 10 subjects. Please whats app or call or visit my website www.excelvbalover.com
Hi Ajay Sir,
what is best ways to Learning VBA concepts step I idea please share you view ..
Please visit www.excelvbalover.com for my online training content. Or on you tube channel... Start with these Playlist... Excel vba introdcution.. Excel vba loop, Excel Vba collection Loops, Excel vba workbook handling, Excel Vba file handling, Excel Vba function, Excel Vba arrays, Excel Vba connect outlook with Excel, Excel Vba Access, Excel Vba powerpoint with Excel...
very nice explanation ajay
hI AJAY , am unbale to find ur series since begining , i.mean.. how to use vlookup in VBA..biginner to advance
Satish Sir form lear More channel tell us to watch your video.
It's Great learning from your end.
My Question To you :
Do we able to create some function which helps us to reverse vlookup instead of using an index match function.
As I see in many Interview the interviewer as the question can you apply reverse vlookup.
So, I can do the thing with Index match function But, I want to show them that, I can also do with the help of VBA developing our own Ulta Vlookup.
To surprise them.
Also Form Mine side i am making a project, with the help of that we can increase a Number of the subscribers Instantly.
You can create your own vba function.. Simple
What is the benefit of this long work if a can do the same work in just a minute by applying direct vlookup formula .
Please explain to me
What is the benefit of vba if you know very good excel?
Think bigger hitesh. It's not about vlookup. It's about automation. Knowing vlookup in excel means you will not learn VBA vlookup. Many times you use these things on auto mode... U have to think beyond excel.... Learn and use it in your own project
Great video a lot of useful information but explained well
Sir.. If have get year also next colume.how do I go it.. Please
Sir, plz make video in hindi for how to merge data in master workbook from any folder (other workbooks)
its very helpful but it had one problem that which is , every time I run the loop it's going back to the oldest data and vlookup it again. this in large data taking a long time , i need it to move cell by cell only and vlookup it .
You can set up range and sheet entries in excel sheet and code can lookup accordingly
@@AjayKumarparmar Thank you for respond , is there examples for it , I rely need it for my work instant of vlookups functions . because it is faster and biter .
@@mohammedhisham7435 i don't have ready made code. If you wish to purchase VBA course you can let me know. Such things need a deep understanding and a proper channeling to be able to cover topics in a sequence
Hi Ajay Thank you. can you also do the same Vlookup to extract different information from multiple workbooks instead of same workbook with VBA code?
It works the same way as it works in Excel without vba dear. You need to use workbook name and sheet name with range. Suppose main data is in workbook "Test" and sheet name is data. You will write.. Workbooks("test"). Sheets("data"). Range(a1 :c100")......... Rest vlookup parameters remain same....
And welcome to the channel... Hope, you will have great learning..
Thank you it is working. i just had one more question. how can you use Vlookup function for cells with special formatting like numbers
Video 343 will help you...thank you
Thank you
Can we add this code while saving
Use save event
I have two data. One from finance dept and & human res development. Its contain id number & employee name. How i can use macro to identified it?. If i use excel formula it would be =if(vlookup(id number from finance dept; table from human res dep; 2;0)=0;"";if(vlookup(id number;table from human res dep;2;0)=name from fa dep;"name and id correct";"incorect name")
check vlookup with loops video under playlist excel vba functions
Oswm sir
yes really wonderful video
Thanks a lot again...
Superb
Ya this one is the best
Hello... what if the range is dynamic? how that willwork?
Hi Jorge... In that case you need to mix vlookup with Loops. You can use for next loop. I have basic and Advance videos on Loops, if you need to get more understanding. Watch Playlist.. Excel vba Loops... Start first with basics.however I will upload the video on same as well... Good luck... Keep watching..
I uploaded video no 275 .. please have a look.
Sir Aapke video bahut achhe h. Mein chahta hu ki aapka channel khub badhe. Lekin India me 5% log English achhe se jante aur samjhate h. Isliye in sabhi topic (according to your video) pr hindi me video banaye. Aapke subscribers bhi badhenge aur aapka channel bhi khub grow karega. Apko india,pakistan,nepal, bangladesh jese desho se bhi khub viewers & Subscribers milenge.Aapne dekha bhi hoga technical guruji, technical sharmaji jese channel hindi me hi h aur khub grow kr rhe h.
Bilkul... Sahi baat hai. Shuru karte hai hindi mein. Jai ho
hello ajay thank you do much for video.I have an problem in vba.my problem is in combobox when I input data in combobox textbox get data automatically. textbox1= c ombobox1 vlookup.
I am afraid if I understood Query..