Macros & VBA Training Series Part 1:
Writing Your First Macro & The Excel Object Model
Double-click video to view in Full Screen HD.
Download the file used in this video:
Macros & VBA Training Series
Enroll for FREE
The video above is the first in a 3-part training series. Can I send you the next two videos?
Please click the link below to enroll in the free training. I will send you video #2 in just a few days.
Please click the big green button above to register for the free training series.
About Me
Welcome to Excel Campus! I am excited you are here!
My name is Jon Acampora and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. When I'm not looking at spreadsheets, I get outdoors and surf. 🙂
What Process Do You Want To Automate in Excel?
Please leave a comment below with your answer, and any questions. Thanks!
JON,
I’d like to get on with the training. Please send me all the material in this sequence.
Thanks,
Raleigh Ormerod
Hi Raleigh,
Thanks for signing up for the free training. You are on the list and I will be sending the videos over the next few days. I space them out to give you time to watch and practice.
Thanks again and have a great day! 🙂
Hi Jon,
Great video(s), I can only mirror the positive comments above. Your presentations are the best I have seen online. Very clear and succinct.
I common process I deal with, is normalizing and cleansing data after I convert a PDF document to Excel.
I will be given many PDF files (each with multiple pages) which contain parts lists with many column headers and records, the being goal to create a single database that can be analysed.
I use a PDF converter and Power Query to execute steps 1 and 3. It is step 2 that is a time consuming process. If I can see a common pattern I will create some formula(s) to re-arrange the data in preparation for step 3. The PDF convertor will often place text into multiple cells, generally below each other and not into one cell.
The data is generally structured, but I must site each sheet and correct any data placed incorrectly. I use Power Utility Pak v7.2 and the “Multiple Cells to One Cell” to correct text that has been placed separately. But this is still labour intensive and I must site each instance which takes time. By using formulas and conditional formatting the site part becomes more efficient, unfortunately this is still slow and prone to effort.
I have scoured the web looking for a better way, also hoping Power Query may offer a solution but with no success. My belief is VBA is the likely solution. Many help or direction will be much appreciated,
Regards,
Spiros
Thanks Spiros! I appreciate your kind feedback. That sounds like an interesting challenge. I believe VBA could help with this, especially if there are patterns in the multiple cells and line breaks. If they are recurring part numbers then you might be able to build a table with all the part numbers, and use that to find/lookup the broken part numbers by first removing all line breaks.
When tackling projects like this, I try to focus on the big wins first. Try to find a solution that will resolve a lot of the issues with patterns. That might leave you with a little bit of manual work to cleanup the rest, but at least the majority of the task is automated.
I hope that helps. Thanks again Spiros!
Hi Jon,
Its been pleasure to know how I can actually start the engine towards my journey of learning macros and VBA.
God bless you for that and keep up the good work, it surely encourage the amateurs like me. I surely am excited like Andy foe the next video.
Cheers…
Thank you Shazif! I’m excited to hear you are starting the journey. I got hooked about 10 years ago, and haven’t stopped coding VBA since. Have a nice day! 🙂
Very informative and helpful!
I wanted to apply for a job and wanted to make sure I understood macros.
Thank you!
Thanks James and best of luck with the job search!
Just need to understand macro for pivot tables to allow to slice data in one chartb
Jon,
I want to develop a macro to filter a column in an Excel sheet and return the list description and count to a sheet labeled “Table” in the same workbook. I started by recorded a macro using the macro editor. When I select the cells in the 1st visible row below the heading (in the filtered list), it returns it returns the contents of the desired cells in the row referenced to the actual row number in the sheet (not the desired cells in the first unhidden row).
If this is unclear, I did send you a separate e-mail describing this problem in more detail.
I did play the 2nd &3rd videos in your course, so I have some idea as to how to set up a for, next loop. I probably should have read some of the questions & answers in this stream before trying to contact you directly, but live & learn. Keep up your good work (which is much appreciated).
Thanks in advance,
Hi Raleigh,
You might want to first select or reference the visible cells only using the SpecialCells method. The following line of code would return the total count of the visible (unhidden) rows in the range.
Range("A1:A100").SpecialCells(xlCellTypeVisible).Rows.CountI hope that helps.
Thanks for explaining the “dots” early on. That was helpful.
I have figured out part of what I wish to automate–automatically updating links in workbooks without opening each file yourself. Now I want to take it to the next level using ranges for listing the files, a user form to select which file or set of files to update, and proper error handling. I am currently “learning” VBA by tweaking code already written by someone else. I know I need to learn the basics so I can actually understand what I am doing!
Thanks Mitch! Yes, learning the basics and foundations of the VBA coding language will save you a lot of time when trying to figure out code that others have written. Sounds like you have a good start on it. Thanks again and have a good one!
I am thinking of automating a process that copy a number of cells from one sheet to another. It will first find the last row that has data, then copy the first 3 columns into a new sheet, while removing rows that have no data. It will then copy the next 3 column from the previous sheet and paste that data below the first pasted cells.
Hey Oniel,
Great use for a macro. I have another video series that explains how to find the last used row, cell, or column in a worksheet.
Hi Jon. First of all, I ‘d like to thank you for providing the video. You’ve been very analytic. I have a problem which is very basic to do in matlab, but I have to do it in Excel. I have two columns: the first includes names and the second a particular number. The number is used for some calculation and the results are in four cells.
Beginning from the easy part, I’d like those four cells (for each name) plus the name to be copied in a new sheet.
The second and harder is if it’s possible to have a loop to replace the name and the number, and then copy-paste the result and the name in a new sheet.
Thanks a lot!
Veni
Hi jon,
First of all, thanks a ton to pros like you on the internet for us lost souls! Its really great learning. I just went through your first video on how to work with Macros using VBA. I am pretty excited and hope to be able to do this in some time soon;
1) Use VBA to extract rich meaningful info from the created tables/pivot tables. kind of like SQL queries to interact with databases like we do in our everyday lives.
2) Automate the above with some sort of a user input form, which will take the user inputs and query the table for the data and display it in a new sheet, chart, etc..
Hope my learning goals are clear and do let me know if this is the right way to accomplish the same and if anything else is needed to be looked at.
Thanks and more power to you!
regards..
madhur
Thank you Madhur! I’m happy to hear you are enjoying learning VBA. Those tasks can definitely be accomplished with VBA. We will look at userforms in the 3rd video in the series. Thanks again!
Hi, Jon! I have volunteered to do some financial spreadsheets for our church. My first sheet lists all the items paid out of the checking account with relevant references. I’ve set up formulas to do necessary math on the sheet itself. However, several “funds” use the same checking account, such as Sanctuary, Fellowship Hall, Parsonage & Cemetery. My next step is to develop VBA code to automatically copy pertinent information to sheets for each fund based on the fund identifier in the master sheet to facilitate reader friendly reports for our Administrative and Trustees boards. That is where I bumped into a wall. I have viewed several videos on internet, but yours are the most straightforward, easy to understand ones by far. I have attempted to adapt examples to accomplish the tasks mentioned above from other videos, but they just don’t work. I look forward to learning from your presentations to help me accomplish the tasks mentioned above. I am 70 years old and a lifelong learner. Thank you for what you do and God bless.
Hi Eddie,
Thank you for the nice feedback. That sounds like a common issue that can definitely be automated with VBA. There are many different ways to solve this issue. You could loop through the cells, check for the account/identifier, then copy/paste the data to the corresponding sheet. You could also filter the data for each account, then do the copy/paste in one step for each sheet. We will talk more about loops in the next 2 videos. Thanks again!
Hi Jon,
I have a probem with the correct numbering of invoices. Namely, I would like to enter pattern for example. KW, KP, and WZ to Excel alone gave the number diagram KW / nr / year. I can write without problems using formulas. The problem arises when the next same designation KW there is an additional example. KW / J / nr / year. How to make Excel distinguished itself “KW / nr / year” and “KW / J / nr / year” and individually numbered start “KW” and separately “KW / J”?
I have become a huge fan of your’s and suggest to my colleagues n friends for online learning of excel.
The VBA tutorial kick-start was great.
Regarding Macro, I am trying to figure out how to enter a similar text in all the visible cells of a particular column (say col. A) when a filter is applied on rest of the columns. Data to be entered only in Visible rows.
In below code, I assumed, Col. A to be entered data (C), until the Col.B is null. But this considers even hidden rows 🙁
—–
Sub query1 ()
Sheets(“Sheet4”).Select
Do Until Activecell.offset(0,1) = “”
Activecell.Value = “C”
Activecell. Offset(1,0).select
Loop
End Sub
—-
Please help me with a code that consider only visible cells and doesn’t consider Hidden rows(when filtered)
Thanks again Vikas! I really appreciate your support and sharing my content. That helps me a lot.
In regards to your question, you can use the SpecialCells method to work with the visible cells only. The following line will select the visible cells only in the range A2:A100.
Range("A2:A100").SpecialCells(xlCellTypeVisible).SelectYou might want to use a For Next loop instead to loop through a specific range of visible cells. On another note, you might be able to use the Replace method to find all blank cells in a range (or range of visible cells) and replace with a value.
I hope that helps get you started. Thanks again!
I have several Excel files with multiple worksheets and multiple filters on each worksheet. One common filter is entitled Week. Every week I download reports from a particular database. After performing some column alignment calculations, I upload the data to Excel, and then go through the process of changing the week date on each worksheet (5 to 10 worksheets). Is there a macro or tool that can be used so that once I change the date on the first worksheet, the rest of the worksheets are updated to the same date?
thank you
Hi Tricia,
Yes, you can definitely use a macro to change the filter criteria on each sheet. I don’t have any specific tutorials on this, but you can loop through each sheet and change the filter criteria of each table/range of a specific column to match that of the first sheet.
Hi Jon,
Thank you very for sharing such a informative and practical example of maros and your style of teaching is impressive.
I have just a question that if I have an excel sheet that is 500 rows and in each row i want to select a certain text and want to past it next sheet.
I know that i can’t automation the select because different text every time i have to select.
But I want to automate that once I selected a text there should be button that will paste that selected text to other sheet, can you help me in this regards.
Many thanks
Hi Ash,
Great question. If the user is selecting the cells then you can do this copy/paste with one line of code.
The following will copy the selected cells on the ActiveSheet and paste them to Sheet2 starting in cell A1. You can modify the sheet name and starting cell references.
Selection.Copy Worksheets("Sheet2").Range("A1")The Selection property references the object you have selected in Excel. In this case it should be a range of cells. Selection can also reference a shape or chart, if you have another object selected.
I hope that helps.
Hi Jon,
Great video!! Very precise and thorough. It has got me started on how to run my first Macro. And I look forward to receiving Parts 2 and Parts 3.:)
Awesome! I’m excited to hear that you are writing your own macros Simran. 🙂
Hi Jon,
I hope you can help me i am not getting how to give range in second sheet while writing macro and below is scenario.
I have set of data in sheet as mentioned below in sheet 1 called fruits.
Fruits Quality 1 Quality 2 Quality 3
APPLE LADY BALDWIN GRAVENSTEIN
KIWI FUZZY 1 ARCTIC 2 NON FRUTING3
MANGO RUMANI 1 ALPHONSO2 TOTAPURI3
GRAPES BARBERA 2 PINOT GRIGIO 4 GRIS 1
APPLE MACINTOSH 2 COX’S ORANGE 3 NORTHERN SPY 3
MANGO AMRAPALL 1 MALLIKA 3 NEELUM 1
APPLE MUTSU EMPIRE GINGER GOLD
APPLE HONEYCRISP LIBERTY PINKLADY
MANGO BOMBAY GREEN, HIMSAGAR GULABKHAS
In sheet fruits status I have only column called only Fruits column A1
I wanted to create a macro if i type Apple it should come all types of apple it should create line under one category like below mentioned.
Fruits Quality 1 Quality 2 Quality 3
APPLES LADY BALDWIN GRAVENSTEIN
APPLES MACINTOSH 2 COX’S ORANGE PIPPIN 3 NORTHERN SPY 3
APPLES MUTSU EMPIRE GINGER GOLD
APPLES HONEYCRISP LIBERTY PINKLADY
Even i have used drop down list ( Validation) option but it is not working.
Kindly help me
regards
Kanaka
Hi Kanaka,
There are a few ways to go about that. You could filter the data on the 2nd sheet, then copy/paste the visible cells only.
You could also use the Range.Find method to find all the matching results. Here is a popular article I have on a Find All Form that uses VBA. It’s not going to solve your exact problem, but does contain code that will help. I hope that helps get you started.
Dear Mr Jon,
today i make my first successful macro .
Just now I understood how many hours I spend for hard work.
I am the CFO of big company and no body knows beater than me : ” TIMES IS MONEY ”
So with your help I’m going to save time and money
Many thanks ,
Sharon Hazan
Sharon, that is awesome! I’m happy to hear you are saving time and learning VBA. 🙂
I Have 1-56 IPA (which i have to give interior color for all )InExcel 2010 and Coding has been done for all 1-56 IPA nw the problem i am facing the problem is i am not able to run the code for IPA57 and further .
At work we download a lot of files to be used for collections, internal reports, open balance reports… I am constantly formatting the the data by saving it as an excel file creating pivot tables. I tried to create a macro by using the record macro in excel but it did not work (most likely because I don’t know anything about macros except I need to learn how to do them. I still can’t even figure out how to save things in a personal macro workbook (I did see you have a tutorial on this subject. I will look at it). The information in the reports I download are in the same order but the amount of entries vary based on the client name you select. I am trying to learn VBA on my own because I really don’t have the money right now to pay for classes. I am very happy you are sharing your knowledge.
I just finished your tutorial My First Macro, I thought it was good, but also felt there is so much more information one would need to learn before being in a position to write VBA code. I will review your next three videos and check out your channel in YouTube, I have already subscribed. I want to thank you for having great presentation skills (so wonderful that you do not say um or so in your videos). Great Job!
Hi Lisa,
Thanks for the great feedback! You are right that there is a lot to learn with VBA. Just like Excel, it is a massive topic. I do have a paid course that includes step-by-step training on the most important aspects to help you start automating processes. It is called The VBA Pro Course. I will send you more info on it in the next few weeks. Either way, I would recommend some type of structured learning like a book or a course. This is going to help you learn the foundational concepts of VBA, and you will be able to use that knowledge to learn coding much faster. I hope that helps.
sir i m confused why add ins tab automatic removed when i open new excel file
Hi Imranali,
That is likely due to an Office Security update that was released in July 2016. Here is an article that explains why the custom add-in ribbon disappears and how to fix it. I hope that helps.
Good morning, Jon !
What’s the best way to automatically resize buttons ( as well as a rounded rectangle that sits behind the buttons as a backdrop ) based on the user’s zoom percentage ? I have a spreadsheet that I created to maintain my list of CDs which uses a number of buttons to activate userforms for data entry, data manipulation, sorting, printing to PDFs as well as to paper, etc. and there’s a line or two in the Workbook_Open macro that maximizes Excel and the spreadsheet and then sets the zoom level based on columns A through M. What I was hoping to do was to automatically size the buttons ( as well as the background rounded rectangle ) so that it is appropriately-sized ( and positioned ) relative to the data on the spreadsheet. Do you have any suggestions as to how to best go about this ? Would it help to send a picture or any samples of my code for clarification ?
Best regards –
Mike
Hi Mike,
Great question! You can determine the visible range on the sheet with the VisibleRange property. Here is the code.
That will return the address of the visible range. You could move shapes to fit within that range. I also have two article that might help.
How to add a Table of Contents Gallery Sheet
How to make your dashboard resize for different screen sizes
Both of those articles contain code that pertains to resizing shapes and sheet sizes with VBA. You can download the workbooks that contain the code. I hope that helps.
to process a survey using VBA
Hi Jon,
I have several worksheets that are sorted into specific categories. I want to select each category, further sort them by a second category, and subtotal each sub-group. Will macros help me?
Hi Althea,
Yes, macros can help with that. You might also want to checkout my Tab Control add-in. Tab Control allows you to sort and reorganize your worksheets with Excel’s built-in sort menus on a worksheet. You can even sort by tab color. I hope that helps.
Hi Jon,
My problem is that I want to extract data from a pivot table in order to use these data for calculating different percentages. It is time consuming having to do this manually.
Do you have tip in order to automate this process?
The real hassle is that the pivot table is not always the same; It’s regarding how many hours employees spend at work. But it varies who works what weeks – for example – you probably get the picture.
Thanks.
Hi Tobias,
Thanks for the question. I do not have an example or solution for that specific problem, but you can target any part of a pivot table with VBA and find intersections in the values area. Here is a great article by my friend Jon Peltier on how to reference the areas of a pivot table with VBA.
Hi great videos! I have a problem withe an Pass or fail command. I want to use cell value in my command.
Sub test()
Dim Measvalue1 As Integer, lowerlim As Integer, upperlim As Integer, result As String
Measvalue1 = Range(“A1”).Value
lowerlim = Range(“B1”).Value
upperlim = Range(“C1”).Value
If Measvalue1 >= lowerlim And Measvalue1 <= upperlim Then
result.Value = ("pass")
Else
result.Value = ("fail")
End If
Range("D1").Value = result
End Sub
Best regards
Trond
Hi Trond,
The result.Value line will return an error unless you have declared result as an object.
You can replace result.value with Range(“D1”).Value in both lines of the If statement. I hope that helps.
Thank you. I actually do need to know this for work–I would like to be able to highlight a row of data if the values in column A are found in column A of another workbook. The videos and blogs have been helpful. Thank you for your help.
Thank you KC! 🙂
Hello Jon,
I am new in VBA macro, I am taking help from your tutorials. You are really very expert in this area. Your tutorials are very helpful. Currently, I am looking for some help. I am having one workbook with multiple sheets. One sheet is having data from Column A to Column AJ, I would like to write a code to identify automatically the blank cell after AJ and enter the column header as COL1, COL2, COL3, COL4 respectively. Can you please guide me on this. I am able to reach to last used cell by using Cells(2, Columns.Count).End(xlToLeft).Select
but now i need to add few column after that. please help.
I just finished your first video My First Macro, It is great! You are an excellent teacher! Your presentation is so wonderful, very easy for beginner to understand. I am really enjoy to watch it. I hope I will review your next two videos. I want to thank you very much for your great job!
Best regards
Jian
Thank you Jian! I really appreciate your support! 🙂
Hello Jon
I want to make a macro, where I put on the first sheet the info from sheet 2…. with the info from each sheet A1 to B45.
and when I put an extra sheet into the workbook, it will add the info automatic to sheet1
Thanks for the nice Macros & VBA Training Series.
I waiting to see the next video very soon.
Thanks Arno
Thank you, Jon. I’m trying to use VBA to highlight a row if criteria in column A is found in column A of another workbook. If you have any tips or can direct me to help, I would appreciate it. I need it for work and I would appreciate any direction. Thank you, again, for your videos and blogs. They are clear and easier to understand, even the VBA tutorials.
Re: VBA to highlight a row if criteria in column A is found in column A of another workbook, I meant to add that I’m particularly having trouble with the line of code that says if criteria in column A is = or found in column A of the other workbook. If you could help with any tips or direct me to any help with that, I would appreciate it. Sorry, thank you.
Hi KC,
There are a TON of different ways to go about this. One easy way is to use built-in worksheet functions like COUNTIF or MATCH. You can use these functions in VBA to loop through all the cells in the workbook and find matches in another workbook. I don’t have any specific articles or videos that cover this topic, but I hope that helps you get started.
I have a spreadsheet that has a formula in it. This formula will tell the cell to go to #N/A if it can’t be found on another worksheet in the same workbook. I want to add to this step, that if it comes up with #N/A it copy pastes to another worksheet in the same workbook.
=VLOOKUP(S3,’Vendor Update’!A:C,2,FALSE)
What do I add from here?
Hello sir,
I am new to this .I want to learn VBA at expert level.
Regards
Rashish Agrawal
Hi Jon,
I want to paste a list of names in sheet 1 (called Name List). I want the list to appear on sheet 2 (Oral Evaluation Form) and on sheet 3 (Final Evaluation Form). I want to paste the list once and to get it pasted on three sheets.
Thanks,
Emad
Thanks Emad!
You might want to checkout my video series on how to copy & paste with VBA. I hope that helps get you started.
Hello,
I am happy to meet u online and thank for sharing us
I do not know how to say my problem
my goal is set a button and then type the codes
do whole I Want with that
but it is not idea and my brain always stuck
Hi ,
I want to import a range of cells(Range 1 ,Range 2 etc)from Sheet 2 to Sheet 1 using a command button for each each Range in Sheet 1.
how can we do using VBA code ,with each range of different size .Can you share code ?
I find your way of teaching awesome ! so how can i join VBA online (professional) starting from scratch.
Cheers
Sumant
Hi Sumant,
I do have a video series on how to copy & paste with VBA that might help you. This video series is from my VBA Pro Course, which I will be sharing more info on soon.
Hi Jon,
I just had finished watching first video of writing “MY First Macro.xslm”. Excellent way of teaching and presentation of video is very nice and made simple for better understanding. I am very eager to look further videos.
Thanks will be in touch,
Regards,
Vinay
Thanks for your support Vinay! 🙂
Hello Sir,
I want to automate my sheet for deleting or filling up blank cells according to the pattern i’have recorded in codes. As i am working on large data and want to use several functions like vlookup, countblank,etc. on the sheet but the sheet is typical as the column or cells are not seems to be quietly filled up in the manner so that i may use functions on it properly and easily.please guide me on this.
thanks & regards
Via VBA, I want to be able to select the frequently changing last row of a table and then copy/paste this row to the first row of another table that has previously been sorted by date (oldest date to newest date). Thus, my copied row replaces the oldest dated row in the new table.
The first table is a list of all golf scores for each member of our golf group (A separate table for each member). The second table is a running list of the last 20 golf scores of each member of our golf group. (Again, a separate Handicap Table for each member).
After a golfer’s latest golf score is copied into his 20 score Handicap table, the table is resorted by score to obtain this golfer’s best 10 scores. The average of the last 10 scores is used to create the new handicap (AverageScore-Par)*.95 = New Handicap.
Hi Vern,
That is definitely possible with VBA. I have a few resources that might help. Here is a video series on copy and paste with VBA. This video series is from my VBA Pro Course. I also have a video series on how to find the last used row in a range. I hope that helps get you started and have fun out there! 🙂
I enjoyed video 1 very much. No one has ever explained the top to bottom step down process of entering code.
You asked what I would like to code.
AC# Name Date Amount
101
Thank you JHC! 🙂
Awesome step by step. I can’t wait to automate my sales reports.
Thanks Richard! Sounds like a plan.
Thanks Jon for the video. It’ clear and easy to start with.
Thanks so much Jon for your good work.
I have learned a lot from the video #1, so I am looking forward to the video#2.
Best Regards
Thanks Jackie! 🙂
Hey Jon,
Thanks for the videos I look forward to the next.
A lot of what I’m trying to automate at the moment is monthly charges.
But I’m finding the problem I’m having is the range is always different.
I’m getting away with selecting a cell using end and down, one to the right and entering a value so my formula can be copied down the bottom of the range.
Thinking there may be an easier way.
Thank you
Adrian
Hi Adrian,
I recommend storing your data in Excel Tables if you can. Excel Tables are referred to as List Objects in VBA. They are much easier to work with because you can reference the entire Table in VBA, and this reference will include any new rows or columns that are added to the Table. Here is a video on an overview of Tables if you have not used them yet.
I also cover Tables with VBA extensively in Module 11 of the VBA Pro Course. I hope that helps get you started.
Very helpful and very easy to understand. Thank you!
Well done, enjoyed your presentation. I am treasurer of our church and presently using a Lotus 123, macro bound
program to list deposits and their designated purpose and checks and their designated purpose, using a numbering
an account numbering system I developed. I want to use Excel so that have after these amounts have been
entered on a monthly basis, Excel will automatically produce an end-of month report similar to the ones I am
producing now. Thank you
Thank you Stan! Sounds like quite a project. 🙂
Hi Jon,
I would like to find the matching values between 2 columns and return a value from a different column much like a vlookup would do. I found this macro, how would I modify it to return a value from a different column?
Sub Find_Matches()
Dim CompareRange As Variant, x As Variant, y As Variant
Set CompareRange = Worksheets(“CustPart#”).Range(“c1:c39190”)
For Each x In Selection
For Each y In CompareRange
If x = y Then x.Offset(0, 1) = x
Next y
Next x
End Sub
Hi Shawn,
There are a lot of ways to go about that. For this particular macro, the Offset property is offsetting 0 rows and 1 column to the right. You can change the 1 to a 2 to return a value two columns to the right. It can also be negative to return columns from the left. I hope that helps get you started.
Thanks Jon,
When I change the offset, it returns the value of x to a different column. I would like the macro to return a value from a different column.
An example would be, when I compare Column A to Column B it would find the match “grape” in Column B1 Row 1 and return the value of Column C1 Row 1 “10”
apple grape 10
orange rice 20
grape bread 30
Where is it going to return the value?
Jon thanks for all your help!
Sorry for the inaccurate/bad example above.
I would have a blank column next to the column of the values I’m matching.
So in the example below, it would return value in column B. I hope this clarifies what I’m trying to do.
ColA…..ColB…..ColC…..ColD
apple………….grape…..10
orange…………rice……20
grape…..10……bread…..30
Hi Shawn,
You can use a VLOOKUP formula in column D for this. You can use the vlookup function in Excel and in VBA. Here is an article that explains more about the vlookup function if you are new to it.
You can also use the vlookup function in VBA with the following.
Range("D1:D3").Value = WorksheetFunction.VLookup(Range("A1:A3"), Range("A1:B3"), 2, False)This would prevent all that looping. I hope that helps. Thanks!
Jon thanks! It works! I had to modify the code a little to satisfy my needs.
Range(“c4:c72”).Value = WorksheetFunction.IfError(WorksheetFunction.vlookup(Range(“b4:b72”), _
Sheet2.Range(“c2:e40000”), 3, False), “”)
I’m looking up the range B4:B72, how can I change this to lookup the active cell/active selection?
I guess what I’m trying to say is I would like to lookup a relative position instead of and absolute position and have the return value match.
So if my active range/cells were B20:B40 it would return the value in C20:C40
Hey Shawn,
You can use the Offset property for that.
Selection.Offset(0,1).Value = WorksheetFunction.VLookup(Selection, Range("A1:B3"), 2, False)The Offset property allows you to offset the Range reference or selected range by a specific number of rows and columns.
So in the example above the values are being returned zeros rows DOWN, and 1 column to the RIGHT of the selection. You can also specify negative values if you want to move up or left of the range reference.
Let me know if that makes sense. Thanks!
Got it….Thanks!!
Thanks Jon and this is a great way for me to start with macros and VBA.
Jack Mouctar
Awesome! Thanks Jack! 🙂
I work with excel spreadsheets almost daily performing data analysis but I avoid VBA because I am not a programmer. I spend a lot of time munging data, creating pivots and then charting. I know much of what I do can be simplified through macros but I just haven’t really wanted to embrace the use of macros until now.
Thank you Judith! VBA can feel very overwhelming to learn, especially if you don’t have a programming background. But it can also be very rewarding and save a lot of time with those routine Excel tasks. I recommend giving it a try and see if you get hooked like I did. 🙂 After you write a few macros and hit the run button, you will be amazed at the speed and power of this tool.
Thanks again!