Excel Macro Class 3 - Looping through Ranges & Working with Cells

MY FULL EXCEL VBA COURSE [35% Discount]: www.teachexcel...
200+ Video Tutorials - 200+ Downloadable Excel Reference Files - 50+ Hours of Content
Excel Macro Class 4: • Excel Macro Class 4 - ...
Excel File: www.teachexcel...
Ask a Question: www.teachexcel...
Excel Macro Class 3 - How to loop through ranges of data and work with the cells in those ranges.
This includes:
- how to loop through a row of data.
- how to loop through a column of data.
- how to loop through all rows and columns of a range of data.
- how to get values from the cells within the ranges.
- how to access any data point about the cells in the ranges.
- how to use IF statements to make logical checks within the loop.
This tutorial covers the For Next Each loop.
TeachExcel.com

[@TeachExcel]

MY FULL EXCEL VBA COURSE (Beginner to Expert) [35% Discount]: www.teachexcel.com/vba-course-update.php?src=yt_pinned_comment_Ws9xskIadSk 200+ Video Tutorials - 200+ Downloadable Excel Reference Files - 50+ Hours of Content

[@georgekammar]

This is the best excel channel out there. Good job man.

[@TeachExcel]

Thanks so much! I really appreciate that :)))

[@Azam_Pakistan]

Endorsed 100%

[@kunalsadhukhan5968]

@@TeachExcel one query regarding the instruction shared on 7.39 Do i need to keep the set myrange line of coding when I am using selection in the next line??

[@shenay9377]

Thank you for explaining this in a very simple way. Everything makes sense. You're awesome!

[@A-broken-clay-jar]

You are absolutely the best on Macro.

[@scottgaines2677]

Great video! You explained the for each loop very well. Things make more sense now. Appreciate it!

[@graememcgregor8407]

I need to see this a few times! For me it has gone from dead simple to pretty complex.

[@TeachExcel]

Make sure to start with the first class in the series. Here is the Macro Class Playlist: kzbin.info/aero/PLxhsXZXQXrUB6Zw8a18hEiJxhgWUxL07i

[@TeachExcel]

Actually, I just saw your other comments, and realized you saw the other videos :)

[@graememcgregor8407]

Yes. The subject matter gets a little more complex here, but that is all good as it is the stuff I badly need to know. I will get it with a couple more looks and going off to make my own examples. Thanks again for your videos.

[@bmkucuk]

I've watched lots of videos for to learn VBA for beginners, and your video is by far the best. You are very good to teach something , congratulation, Thank you for your videos

[@adumcoca5847]

spent two hours fumbling on a loop, then found this and fixed it in 5 seconds. thanks!

[@koyelbhattacharjee5208]

Your tutorials are really awesome and very elaborate. Great! Cheers!

[@tannertucker22]

You teach very well. Thank you.

[@TheMrDrinken]

Best excel courses on the net. Thank you!

[@noobnoob5132]

Thank you for your enthusiasm on teaching this topic. Youve been helpful

[@xiaoyuwang979]

Great video! So helpful for my job hunting preparation.

[@bogamcs]

I love your voice and style. It's like listing to Salman Khan on Khan Academy. Thanks for the tutorials!

[@TeachExcel]

hahah Thanks!

[@Toitoikoi]

thank you so much you explain so neat its just easy to understand ..

[@avpproduction381]

Thank you for making my life easy.. god bless

[@jonathanfraga3771]

Thank you! Excellent video.

[@Hanyoungiee]

Quality content 🙏 Thank you very very very very much sir

[@spkadekodi]

Awesome helpful video.. Thank you!

[@TeachExcel]

You are very welcome! I'm glad you liked it! I'm also almost finished with the full course now! If you're interested, follow the link to signup for a message when its released :) www.teachexcel.com/vba-course-update.php

[@shivkumarr9060]

exhausting but run once or twice and you have it clear.

[@hazemali382]

Many Many Thanks Mr.

[@vijaysahal4556]

Nice 👍🏻👍🏻👍🏻👍🏻

[@tannertucker22]

Excellent. I purchase many of the Udemy courses, but yours are presented better and more logically. Thank you. I would love love to know how to loop through a range AND compare a value in the range (let's say in column B) to a named range (a list of postal codes). If the row's column B value matches any of the values in the named range then do something. Any direction would be greatly appreciated. Thank you again.

[@TeachExcel]

Thank you for the compliment! I'll keep making tutorials if you keep watching em :) Basically, there are a few different ways to do what you are asking and it depends, I would say, on how large your data-set is in Excel. If you ask this question on our forum (www.teachexcel.com/talk/microsoft-office) I can give you some examples and a sample file as opposed to just pointing you in the right direction in a comment here.

[@gerardvaneggermond2067]

Hello sir, I found it interesting even though I don't fully understand it, maybe you can help me, I made code but it's long, can you help me make it smaller?

[@AI_CANISTER]

This sound just like Python 💫

[@hazemali382]

please we need more and more videos for VBA we need to know how make business (handle massive date )with VBA

[@girifilmmaking6240]

Hi, thanks for nice tutorial. I have a datasheet with 50rows . And. I have another sheet with userforms. My intital plan was to enter all those data manually, which I can do successfully. But now I think, For each row of those 50 rows I am manually entering in to userform 1 or userform 2 depending on the criteria in column B of my datasheet, why can't I automate that as well. Like, For each row, if colum b cell... Then call userform 1, Next... Do you think that sort if looping us possible?

[@mechsparks]

very help video

[@iamnotmadok]

I think it goes from simple to pretty complex in my opinion because I need to figure out what exactly c is. Please correct me if I'm wrong, but I think "c" is = the object "Cell" but has to be declared as a variable to work in the for loop. are my declarations and Set values right?: Sub LoopTest() Dim c As Object Dim myRange As Range Set myRange = Range("A2:A4") Set c = Cells 'For Each c In myRange For Each c In Selection MsgBox c.Value Next c End Sub

[@Seftehandle]

great video really loved it. One question: how "c" was recognised before you declared it at the end?

[@TeachExcel]

VBA in Excel is great that way! You don't actually have to declare the variables, which is what I did at the end. The program itself will figure out what kind of variables they are. So, the program figured out that the variable "c" was a Range variable when the macro was run. If you make big macros you always want to declare the variables, but, honestly, if you make small macros just for yourself, you don't have to declare them.

[@TeachExcel]

And thanks! I'm glad you liked the tutorial :)

[@Seftehandle]

OMG that's such a beautiful answer. Thank you so much! The video is perfect and so easy to follow. The comments help to not have to stop and take notes. I can't wait for new macro videos!!!

[@TeachExcel]

:))) I hope to make a series that is good enough so no one has to buy any more of these super expensive courses online. If you have any more specific questions where you want to upload files or anything beyond youtube comments, you can always ask on the forum as well (www.teachexcel.com/talk/microsoft-office). Also, feel free to give me constructive criticism or suggestions on topics to cover for future macro tutorials! :)

[@TanvirMostofa]

Dear brother, I want to know how to solve the three criteria against floor value index match function in visual basic ? Can i shear a excel file.

[@SarathSankarV]

What is difference between Module and Class Module

[@jimmylao8509]

I would want know the module that we wrote , they wont be there for next time when we open the excel ?? that is what happen to me. I could not find the CODE that I wrote yesterday for class 1 and class 2

[@blembones]

can we use the "selection" keyword while taking selecting from the user via kind of input form early response much appreciated mate !!

[@ravipertab5496]

A beginners question: Can you explain how the software knows that you have defined "c" to mean "cell"?

[@TeachExcel]

No worries, it can be confusing. This line does it: For Each c In Selection Selection contains a kind of "group" of items and each item in that group is, essentially, a cell. The line of code here simply says to make the letter "c" equal to the current item from that group. This is how "c" becomes a cell reference.

[@TeachExcel]

Don't hesitate to ask! Otherwise, you'll never learn! :)

[@ravipertab5496]

Thanks a lot for that explanation. On to class 4!

[@grimsonforce7504]

For some reason the If statement doesn't work at all. The text is formatted as general. Msgbox will work for anything else but if place the contents in an "If" statement nothing happens.

[@joymyespenedogorra5345]

Hi I've been following your tutorial now and i would like to ask if , there's a way to create a Macro and how to code in this kind of example.. We have a monthly liquidation from store So. We manually encode all Sales in excel in our format by Filtering or find..the Sku's or Item code Manually. n imagine we have 300 Stores, so tiring. I hope you can Help me with this.thank you so much.

[@Ayappasvamisongs]

Hi, Thank you for posting this vedio. I have created one macro, please help me how can I put into loop Ex : I have tabel of date am searching one condition if it is March I need print enteir row as it is.

[@TeachExcel]

Please ask this question in our forum: www.teachexcel.com/talk/microsoft-office?src=yt_comment Make sure to upload a sample file along with a detailed description of the issue so someone can help you :)

[@umarshah7179]

Hey thanks very much for the videos , just wanted to know can we use the offset formula without Msgbox. If yes then how. Or is there any other method used to tell excel to move to next cell after executing the macro

[@mohibuddin]

Range ("Cell reference").select

[@thetartarianfederation]

Do you have to use "set" when declaring a variable because in the previous video you just made a variable without using "set"

[@TeachExcel]

Only if you want to "set" the variable to an object - think about it like this... You "set" a variable equal to a cell/range reference and you can then use that variable as that cell/range reference. However, when you simply do variable = value then the value must be something simple like text or a number and then that variable will be the same as that text or number value. This is a simplified explanation but I hope it can help some.

[@Oheky]

Hi I really like your video content it has opened my eyes of what excel has to offer. But I have a question. Is it possible to link several work sheets into one work sheet by the change of the background colour. I would presume it would be a macro. I work from 3 different style work sheets in the same workbook and enter similar data in all three but at the end of the month need to add only the red coloured data from each work sheet to the monthly sales work sheet which is time consuming and fraut with mistakes. So I was just wondering if it's possible. Can you help me please.

[@TeachExcel]

Yes, you probably need a macro for that. Go to my forum and ask there and you can get help. Include a sample workbook when you ask the question so that it will be easier to understand and help. www.teachexcel.com/talk/microsoft-office?src=yt

[@MMAMedia-hh7sn]

I copied the macros down to - MsgBox "Cell " & c.address & " equals green." but for some reason there is no msgbox coming up when I run it. I have checked and double checked that my text is written the same as yours, but it just won't work. And of course there is no error msg, do you have a clue why the msgbox won't appear. I have the cells selected by the way. thank you

[@MMAMedia-hh7sn]

Just figured it out. I used a Capital letter for Green in the Sheet containing the data!

[@TeachExcel]

Glad you figured it out! Sorry I couldn't reply too quickly, it can be hard to keep up with all the comments.

[@jlpayne052710]

On the section where you have the If statement and Msg Box, each time I run it I get "Can't Execute Code in Break Mode, I've gone to the Debug menu and cleared all breaks but can't get the error to go away what am I missing?

[@TeachExcel]

Go to the menu option Run > Reset and that should work. Break Mode is where you end up after an error has happened or you want to analyze the macro and you need to Reset the project to get out of it.

[@MrDadcard]

You could of wrote " equals " & c.Value

[@antoinetteharris6427]

Very helpful tutorials 👍 Have a question though, when running the If Statement I get an error that says " Can't execute code break mode" , please advise

[@sahilagarwal1041]

That's coz your debugging mode is on. Try turning it off. And then running the macro.

[@mayraruiz9761]

How do you turn off the debugging mode?

[@brye953]

On the very first example, I Get{ run time error 1004 Method'Range of Object_Globalfailed}

[@a.bafakih]

If I want to generate a loop to check what ever user enter a value from a text box and create a button to check the user entry value with a pacific column in Excel. I need your help regarding to that.

[@TeachExcel]

Sorry for the late reply. Go ahead and ask this on the forum and provide a sample file and we can figure it out. www.teachexcel.com/talk/microsoft-office?src=yt

[@Azam_Pakistan]

Can I download all the programming code being taught?

[@TeachExcel]

Link to it is under the video :)

[@TheTraveler2222]

where are the workbooks? have they been removed?

[@TeachExcel]

Watch the first 50 seconds of the video...

[@TheTraveler2222]

@@TeachExcel Thank you!

[@montasralkady]

why Excel on Mac , does not speak cells ?

[@schiesarovincenzo7613]

Voglio sentere parlare Italiano

[@Abhinavkumar-og3xd]

Please speak in hindi.