Excel VBA .Cells For Beginners Part 1 of 4

The Excel VBA .Cells technique is incredibly powerful - and slightly confusing! Learn it and use it with this series of four videos.

Link to download file:

In the first video, we deal with the basic concept and apply .cells to select a single cell. The .cells technique uses numbers to reference cells in the spreadsheet; this makes sense for rows (we are used to using numbers to refer to them) but less so for columns. So, we first use an =column formula to show how Excel can reference columns using numbers. Cool!

After opening up the VBA editor, we attempt a simple application of .cells. Chris explains the basic cells(a,b) concept where 'a' determines the number of rows down, and 'b' the number of columns across. Armed with this concept, you can use .cells to select a cell in the spreadsheet. Remember to try different combinations - it should feel like you are 'playing' with the code; this really is the best way to build confidence.

Finally, we introduce the idea of the 'anchor point'. By integrating a cell reference before .cells (eg. Range("A1").cells(1,1)), we can 'anchor' the cells technique anywhere on the spreadsheet. Chris demonstrates this by using A1 as an anchor point. Those experienced with .offset will notice a subtle but important difference in the referencing here, which Chris explains in the video.

Hopefully you are beginning to feel the power of the .cells technique! How did you get on, and what applications have you found for .cells in your work? Please leave a comment.

EXCEL VBA .CELLS SERIES RELEASE DATES

Playlist link:

These introductory videos will be supported by 'long-play' application videos showing how to apply the technique to real-world problems. These will be viewable in the series playlist when released.

Get in touch with Tiger using the platforms below!

Mailing List

Also on Insta :-)

[Nessus]

Chris is back with new VBA videos! feel like I'm no longer in a time-delayed state! - Ray, USA.

[maurocastagnera]

Thanks Chris, great video! I hope you'll make a course step by step from beginners to advanced because it's really a pleasure listening to you, very clear and simple 👍

[rayhanrana]

Tons of thanks you Chris.All of you created videos are really great. Your videos become more easier if you represent more more videos on VBA basic stuff. I find video really useful.

[thelonewolf]

Can you do a video about writing/reading data from and to a .txt-file? Put it in "c:/temp/"database" etc.

[jellypeanutbutter]

Incredibly clear and helpful. Thanks very much! Do you have any online courses available (you have some excellent material in YouTube, I’m just wondering about MOOC content). Thanks as always!

[RInvestments]

Hi Chris, good video! Can you please let me know what kind of micro you have on your desk? Thank you.

[Martiny-mrtiny]

Do you know why coordinates of .cells are (y, x) and not like all the mathematicals (and excel) consensus (x, y) ?

[AlexeyZubkov]

How to select item from list based on its description(aka keyword)?