VLOOKUP Using VBA
HTML-код
- Опубликовано: 31 янв 2015
- How to use VLOOKUP with VBA and automate calculations.
Details: www.exceltrainingvideos.com/vl...
For more knowledge read the book Excel 2016 Power Programming with VBA: amzn.to/2kDP35V
If you are from India you can get this book here: amzn.to/2jzJGqU
I've been using V-LookUp formulas, if then statements,, and pivot tables in Excel, but seeing them being used in VBA has open up a whole new way of taking them to a new level. Thank you!
That's great!
Mr Takyar. I am researching the net for various ways of doing things with VBA XL. What has impressed me with your 'style' is that your tutoring is what I would call The Best to date. Clear slow so the message sinks in and on the top of it as a cherry on the top, all works. I wish others copy your style so they also manage to pass their knowledge to the once that seeks it.
Keep the good work up.
Thank you very much!
You are a legend, kind sir! Ten minutes of this video beat two hours of searching StackOverflow -- thank you!
You are welcome. Please share with your friends also.
This is great and exactly what I need.
Thank you!
Thank You. By following your tutorial I was able to achieve what I've been trying to do for ages
Harry
Thank you for this lesson. It's helped me greatly at work.
This is great exactly what I need. Thank you!!
Sir thank u for providing us knowledge about vba. Excellent sir. Thank u.
Sir,
Its very helpful, interesting and summarized video for beginners like me. I would like to specifically appreciate and thankful for sharing the code details in your website.
Kindly keep it up for educating us. God Bless you.
Thanks for your video, it helps me a lot!
Thank you very much Dinesh, I'm appreciate your teaching on youtube
thank u so much sir... you r really really grt you r d champion of vba
thank you again, nicely taught , made it easy sir. thumbs up
your videos are amazing
Thanks for sharing
Keep it up!!!
Glad that you find my videos helpful!
Thanks for the above video. I would like to know if we can use If,ISblank & vlookup function together. I have a formula in Excel like " Grace Start Period(Date variable)=IF(isblank(vlookup(V2,tracker,8,false)),"NOT SET",vlookup(v2,tracker,8,false))
Great tutorial. Thank you!
Once again, very helpful! Thanks greatly!!
Thanks
DINESHJI YOU ARE GREAT I LEART VBA LITTLE BIT BECUSE OF YOU KEEP GOING
Hello Mr. Dinesh, would you know how to select then copy a variable from a vlookup in VBA? I tried using: Var.select, Followed by selection.copy and started with Dim Var as Variant.
Thank you very much for the help.
AMAZING. Thanks.
Can you do a vlookup from another closed workbook (yesterday's file). I always have a new workbook telling me the amount of inventory. And everyday i need to compare what we have today versus yesterday. My lookup value is a order number that changes everyday. The column headers have the same names
Hi dinesh,
i am having one query. i want to compare two columns ex( one row cells value with another whole column and we found the matches return true other wise false). how to do it in VBA. can you pls guide
thats helpful. thank you sir
very nice...and thanks for enlightening
i m so interested to learn on it sir hopefully u can share me again ur knowledge sir
in reconciliation, I want vlookup from one sheet to other sheets and reverse for reconciliation can you help
Very interesting facts on VBA, very helpful.
Please share the VBA tutorial with your friends too.
Hello, and thanks for the video.I am not new to excel but quite new to VBA, and I would kindly ask for help how to automate process of selecting data from worksheet “report” to worksheet “destination”. I am able to adjust worksheet ‘report’ so that I can select all names from column “A” to sheet ‘destination’. Name should work as source for other data, but not the
only one. Based on the criteria in worksheet ‘destination’ I need to count all breaks for specific name. Breaks consists of break, lunch, restroom. Does you or someone know how to combine ‘vlookup’ and probably ‘hlookup’ together with loop in vba or is there better solution if each name does not have all that needs to be counted? Rnge of names is the Limitation for loops. If someone has an idea I can send xlsx sheets for consideration and part of my vba code.
Hi Dinesh, If I want to VLOOKUP other workbook's worksheet, how can make it work? Thank you for your share, that's very nice.
Sir, please help i have recorded a macro for vlookup but the challange is that it is not going till the last row. it is only calculating till the cell i have recorded it. how can i change it till last row
Hi Sir,
I've developed a form & I wanted to apply lookup function for text box. But the challange is it works only when the userorm is opened in front of the worksheet where the the data is.
If the userform is in from of some other workbook it gives an error.
Plz help
Sir
I crate three button for ex a,b and c if A1 = a so that b and c button should be hide only a button should be show only how to possible in vba pls help
Hi There... how would do this if the data you are looking for from vlookup is on a different worksheet to where you want it to output to?
Eg say we have sheet A values to use for vlookup from sheet B to return values from vlookup to sheet A
My latest video will help ruclips.net/video/e_tDrx3zz34/видео.html or search www.exceltrainingvideos.com There are many videos on this topic.
Sir, your tutorial is very clear and I can easily understand...thank you sir.
I have a problem using Vlookup to return the value in the cell I lookup along with the cell colour background.. would be appreciated if could make a video eoth reagard the above problem....best regard.....J.Hermawan, Jakarta, Indonesia
Let me check. In the meantime you can record a macro and check the code.
@@Exceltrainingvideos Thank You so much for your prompt response, Sir.......hopefully the video tutorial would be available shortly...
Best Regard...J Hermawan.
Sir, thanks for your valuable videos for the ms excel enthusiasts.Well, I am looking for a vba code for general journal posting to general ledger to the respective accounts or accounts head. please.
Basically you need to create multiple worksheets and post from your 'journal' the relevant data to the specific accounts worksheets. This link will help: www.exceltrainingvideos.com/copy-paste-multiple-rows-of-data-from-one-workbook-to-another-using-excel-vba/
Hi! I just want to ask what data type i will be using if my lookup value in a textbox contains more than 15 numbers. I received a runtime error 6 every time i run the macro. Thanks in advance for your response. God bless!
Try 'Single'.
Thanks!!
God bless you sir!! please i request to you make many more videos on vba automation!!!
Thank you sir.
Sir,Namasthe, then explain...the same... to write the code for userform.Tnq... Ravikiran.
sir what if the name william is not unique sir in the data record i mean the name william contain another list of data is it possible to display all those information sir using the v look up function?
But if I want to extract from another workbook using vlookup then what would be code?
Dear Sir, can I combine Vlookup with index and Match, how do I do that?
hi sir, thank you for this video presentation. here I need one more vba code for this formula
=if(a2=VLOOKUP(a2,table1,1,true),VLOOKUP(a2,table1,2,true),"NA")
applying this formula for the above 4 lacks data we vlookup the values from the next sheet table data
waiting for your code
thank you.
Hi,
How can we select a specific cell for lookup value e.g A2
Zain Ali range function
Hi, Mr. Dinesh , i need your help , when i try to use this sentence displayme an error 1004 " Unable to get the Vlookup property of the the Worksheetfunction clas" i verify all the line and sintaxis i dont find what are wrong, thaks for help
Have a look at this link: www.exceltrainingvideos.com/tag/vlookup-using-vba
Hello Sir,thanks for the video, I'm just curious to understand that even if we did not declare myrange the code still exhecutes? Can you clarify plz?
Excel automatically assigns it to a variable called variant and if you many undeclared variables the macro will run slower. Refer to this link: www.exceltrainingvideos.com/why-it-is-important-to-define-variables-with-dim-in-excel-vba/
@@Exceltrainingvideos sir thank you so much
Hi teacher.I have the private school. but I want to manege with VBA. Can you help me?
AOA respected sir! I found your videos very useful regarding excel problems. Sir, I have one problem which i couldn't find on you tube, if you will solve it i shall be very thankful to you.
I have two sheets in a workbook and i want to lookup for three values in sheet#2, which should be equal or greater than a certain value in sheet#1.
Use 'IF' condition with 3 checks like so:
If a>b and a>c and a =d then
do something
end if
Dear Mr. Takyar
I used your above code to vlookup a data set of 750 employee_codes; however, it gives error 1004 as you mentioned above. The issue is that it looks up the value for the first few cells and after an error, it gives a message box and doesn't resume function for the remaining data sets.
Please help.
Can you share your code? It doesn't matter whether you have 750 employees or 750000 employees!
Evening Sir.
I am able to fix this. Silly mistake. I forgot to mention “on error resume next”.
I am working on a project and it will take 2-3 more days. Once done I’ll show you the complete project
Hello Ankur Sir, Can you please share your contact?
hi dear Mr. Dinesh I hope to fiend answer about my comment... thank you so much
Which comment?
Thank dinesh very nice tutorial 👍
Welcome 😊. Please share with your friends too.
@@Exceltrainingvideos will do. Looking forward for more VBA tutorials
I want to automate 3 excel sheets, but unfortunately without success. I hope you can help me out. Thank you in advance!
Thank you very muck. Cảm ơn
Welcome!
Hi Mr.Dinesh Kumar. I am stuck with a problem in Excel VBA as I am a newbie.
I am making a Fee Collection Worksheet in Excel. The problem is
I want to make a Userform in which it could search the Registration No of a Student and then by selecting a Month from a dropdown list and entering the fee. It should automatically update the fee in that specified cell using the registration no and month.
Note: The data is in column like
R NO, Name, Aug, Sep, Oct
1001, John, 1000, 1000, 1000
1002, Smith, 1250, 1250, 1250
Hellp SIr , Need a small help. I am creating Pivot table and corresponding chart through Macro , everyhing is going well except the below problem.-Actually if there is no values in the base table columns then during Pivot creation its taking Default 1 in all the respective columns, below is my code .PivotFields("Release Name").Orientation = xlColumnField
.PivotFields("Period End Date").Orientation = xlRowField
.PivotFields("Data)").Orientation = xlDataFieldso if "Data" column is Blank , still during pivot creation its taking 1. Please suggest
You can exit the sub by using an IF condition for no data in the columns.
Sir , there is a mixup of data , means in some columns data is there and some are blanks
wow nice video very very helpful
Please share with your friends too.
Hello sir.can we write macro for vlookup from two different Excel file?
What have you tried?
Hi my error handler is not working! am i doing something wrong?
thank you
Hello I want to make a form with three combboxes
The first do search the row
The second do search for a column
The third can edid the intersection of the row and the column
I have 2 combo box now i want, if I change any of them other should change automatically like VLookup...
Combo box depending on each other....
This link will help: www.exceltrainingvideos.com/tag/create-dependent-combo-boxes-in-excel-user-form-with-vba/
Or search www.exceltrainingvideos.com
sir,
in method 3 I'm getting Error 1004
kindly help
very good sir 👍👍👍
Thanks and welcome. Please share the Vlookup VBA tutorial with your friends.
I need a code in vba that pastes a range from sheet 2 to sheet 3 automatically when any value in that range changes
Please help.
Nobody died till date of doing some search on my channel or website.
how can I do a vlookup in vba with 2 search criterias and with using diffrent lookup sheets? Please, I need urgent help!
I would say to you that using de the index function is the right way
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.
Please search my channel using the keyword 'report'. You'll find at least 10 videos related to your query.
Hi... I am struggling with a very simple problem that I don't seem to understand whats happening!
I have a dynamic range in excel sheet called "placom" the first column with a list of Platforms like "Zelle" "PayPal" "Venmo" etc, the second column the Fee they charge.
If in the userform cfpla variable lready set with one of the platform names, lookup the corresponding fee, write it in excel sheet as variable cffee and calculate the cfnet wich is the result of the amount set on the userform under the name of cfamo minus the fee.
___________
'Calculation of Fee and Net Income
If cftyp = "Income" Then
cffee = Application.WorksheetFunction.VLookup(cfpla, Range("placom"), 2, False)
cfnet = cfamo - (cfamo * cffee)
Else
cffee = ""
cfnet = ""
End If
CF.Cells(nr, "F").Value = cffee
CF.Cells(nr, "G").Value = cfnet
Call ResetForm
_________
For some reason I am getting cffee "0" and cfnet = cfamo (this is a result of being cffee 0
Any clues?
Thank youuuuu
Can you share the file at takyardinesh@gmail.com?
Sir, how can I get vlookup value in next blank column???
Thank u....
Assign the value to the next blank cell. I have a number of videos on how to find the next blank cell, column or row.
please make a video for vlookup to find value based on column headers if headers change the value changes automatically
You can use Vlookup with Match.
Sir able to do in excel but not able to do the same using vba for dynamic operation
let it go sir, I have created it through vba.
Hi
Can a MACTH function be written in macro.
+Tom Jone Yes.
+Dinesh Kumar Takyar
Excel Mac 2011
On sheet1 database on sheet2 Match Information I like to Match: name, date hire ,ID#, age, salary, year with company.
Thank
hello sir, do you have a tutorial about how to solve error "procedure too large" it seems like my code in one button cannot fit anymore
Split your code into many modules or sub-routines.
Dinesh Kumar Takyar hi sir, I am actually having trouble on how to do it, if you have a sample let meknow
Hi, Sir. I have to code it in one button, do I double click the button so Private Sub button1() then put sub equation1() underneath it?
This is what i need
Glad the Excel VBA tutorial was helpful. Please share with your friends too.
Hi sir can you please explain how last row works? Thanks in advance
This link should help: www.exceltrainingvideos.com/find-next-blank-row-for-data-entry-using-vba/
Can you show me how to do the reverse lookup?
Can you give an example?
مرسی
Awesome
Please share with your friends too.
thanks
Sir you saved my life
Please share with your friends and on social media. You can view more videos in Hindi and English at www.exceltrainingvideos.com/
Sir, do you have any video on vlookup using dictionary in vba
No.
Hi Are a developer or programming engineer ?
THANKS
The Pivot Cache is held in Excel's memory.
Hi Sir,
Thank you so much for uploading these videos - they've helped me tremendously with Excel.
I do have a complicated problem to solve and I was wondering if you can point me to the right direction as to which videos to watch? Or perhaps you could tackle this problem if you fancy a challenge.
So I've got 5 product codes, and I've formulated 5 different unique excel formula codes for each product. For example,
Product 1codes to calculate the position is =if( I$I$>$D$D, "BUY", "SELL").
Product 2 codes: = IF ($I$I>$C$C, "DECISION", IF ($I$I>$G$G, "SELL", IF ("$T$T$G$G, "POINT", IF ($G$G=$A$A, "MAYBE","-")
Product 4 and 5's codes are equally unique and different.
As you can see, it's very unique codes and contains nested IFs etc.
Now I've been copying and pasting these individual codes into separate spreadsheets based on the product (i.e. if it's product 1, I'll paste product 1's code into the cell).
I've actually done up a LOOKUP table in the hopes of using VLOOKUP to paste the codes according to the product column.
e.g.
PRODUCT /// CODE TO PASTE
This is not possible with the usual =VLOOKUP excel formulas however, I was wondering if this is possible with VLOOKUP VBA? Or do you think I should use multiple nested IFs then on VBA? i.e. If COLUMN M is "product 1", calculate in COLUMN N with this formula "=if( I$I$>$D$D, "BUY", "SELL")."?
Sorry for the long message, I'm at my wits end at solving this and hope you'll be able to help - thank you for your time!!
Best,
Natalie
Sir, how to remember coding lines exactly. It's confusing
Remembering code is difficult. If you understand the problem and code line by line it's easier. Of course, you need practice. Intellisense in Excel VBA helps a lot.
Sir
Pls give vba for hide or unhide button on cell base pls
Question not clear.
Hell sir
have a good day
I have multy no of sheats in a workbook
as Tax invoice
original for reciepient
duplicate for transport
Triplicate for Supplier
Extra copy
Estimate
Proforma invoice
and others
Now i have a worksheet (Info)
When i put a logo in Info sheet in a specific cell
it should be replaced with the existing logo in all sheets
Thanks again
Interesting question. We'll work on this.
Ok
gREAT
+DEEPAK MADHWAL Thank you!
Sub se mushkil coding ap k video mn hoti ha
I try to code according to the steps. Once you practice you can optimize some of the lines of code.
where is the code
Get it here: www.exceltrainingvideos.com/vlookup-excel-vba/
Do you use facebook ?
Yes: facebook.com/dtakyar
@@Exceltrainingvideos yes. I have sent content to support via messenger