ไม่สามารถเล่นวิดีโอนี้
ขออภัยในความไม่สะดวก
Excel VBA: Referring to Ranges & Writing to Cells (Range, Cells, Offset, Names)
ฝัง
- เผยแพร่เมื่อ 18 ส.ค. 2024
- Join 400,000+ professionals in our courses here 👉 link.xelplus.c...
Explore the various methods to write to cells in Excel using VBA. Understand the nuances of using Range, Cells, Offset, and Defined Names for effective cell referencing.
🎓 Get access to the full Excel VBA course here: www.xelplus.co...
💡 Key Takeaways:
▪️ Different Referencing Methods: Learn various ways to target cells in Excel VBA for more precise control.
▪️ Practical Examples: Step-by-step demonstrations for each method.
▪️ Tips for Efficiency: Tips on how to optimize your VBA code for better performance.
Referencing ranges is a common task in Excel VBA. It's important to be aware of the different ways you can write to Excel cells with VBA or macros. The macro recorder has its own preference when writing to ranges but it's not the only way.
You'll be surprised at all the available options. Each method has its own advantages. They will become more obvious later on when you learn to loop inside a range in Excel.
You'll learn how to use ranges, cells property, offset, named ranges, difference between activecell and selection, and how to reference entire rows or entire columns and also how to use autofit in Excel VBA.
Practice along with me by opening up a blank Excel spreadsheet. Make sure you close other spreadsheets as we'll be writing to the active Workbook.
Written Article: www.xelplus.co...
This Excel VBA tutorial for beginners, is part of my "Unlock Excel VBA and Macros" course. Get it here: www.xelplus.co...
0:00 - Introduction: Overview of cell referencing techniques in Excel VBA.
0:56 - Creating a Module: Setting up for VBA coding.
1:58 - Understanding Active Cell vs Selection: Clarifying key concepts.
3:30 - Using Range and Cells Properties: Basic methods for cell referencing.
6:54 - Advanced Referencing Techniques: Combining columns and rows.
9:18 - Using the Offset Property: Shifting reference points.
11:31 - Offset with Ranges: Applying offset to a range of cells.
12:06 - Utilizing Name Manager: Assigning names for cell referencing.
13:28 - Rows and Columns: Adjusting row heights and column widths.
15:33 - Summary: Recap of all referencing methods.
★ Links to related videos: ★
Learn about Visual Basic Editor: • Excel VBA tutorial for...
Recording macros: • Excel VBA: Copy Data f...
Properties & methods in VBA: • Learn How to Use Prope...
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creato...
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.co...
🎥 RESOURCES I recommend: www.xelplus.co...
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#ExcelVBA
![น้าครับ..ผมรักน้า EP.04 - ปีกแดงเดอะซีรีส์2024[ฟินมาก] บทซีรีส์สร้างขึ้นเพื่อความบันเทิงเท่านั้น](http://i.ytimg.com/vi/m9Q6MwoWgaQ/mqdefault.jpg)
Get access to the full Excel VBA course here 👉 www.xelplus.com/course/excel-vba-excel-macros/
VBA allows to specify ranges using the short notation with square brackets instead of the Range() or Cells() property. For instance 'Range("A1").Value' can be written in short as '[A1].Value'. This is also true when using areas, for instance [A2:C2].Value = "2nd", and named ranges such at [LastOne].Value = "10th".
Leila has the best Excel tutorials/videos by far! I did two of her courses and she is simply the best instructor!
Wow, thank you!
omg. I'm not getting much sleep. I keep learning more from you and instead of fixing the basic stuff I have written, I am thinking of other things I can do! Thank you :-)
In Spanish-language there's not channel like this one. Great, you have a nice voice, even my native language is Spanish, i get all you explain. I just founded you. Congratulations "Desde américa Latina, Nicaragua. Saludos"
Thank you! 😃
GOD bless you Mrs Leila, Thanks a lot for your tutorials
Hi Leila, Really really impressive explanation of cell referencing. This is the best video explaining the cell references.
Thank you.
Hello Leila, I've been working now through some of your great tutorials here and now I really have to make you a big compliment.
Thanks sooooo much for the great work you a doing here, it's really a great performance.
Your tutorials are very well structured and very easy to understand and extremely helpful. Thank You :-)
I am one of those who believe that there is always something to learn. And this has just been verified once again. Although I have been writing VBA code for years, I have never used the shortcut "?" in the immediate window because I used to write the entire debug.print command. So thank you very much Leila for this
Hi Mehdi. I'm glad you found something new here. Actually I was also not aware of the "?" method for the longest time after I started to use VBA. It made me very happy when I discovered it :)
Hi Leila, for the history this is an old (legacy) command coming from old versions of Basic. If my memory is good I used it with gwbasic for more then 25 years ago :)
Thanks for the info :)
I think I started to understand VBA with you thank you so much Ms. Leila
This is really great Leila. Love your presentation and excellent explanations. It is very well thought out. You make it easy to understand. When Mike Girvin is following your videos, you know you are among the great. Thank you so much for sharing.
Thanks Leila,
You just cleared up a lot of confusion I had about vba range referencing. I am very grateful......and YES, I like what I see but someone already subscribed before me! :)
Glad it was helpful!
New to VBA. Most useful video I have seen. Thank you.
I'm very happy to hear that Lawrence. Glad you like the video.
Lady Leila, I LOVE YOU for making this wonderful tutorial. Excellent TEACHER.. knowing the art of MAKING UNDERSTAND.
Thanks a lot.
I'm very happy to hear this Aamir! Glad you like the videos.
@@LeilaGharani i started watching your tutorials 3 days back and the more i am diving in, the more i am learning "FAST" and more i am liking your WAY of teaching. Full of confidence, command, knowledge and patience. GREAT WORK and inspiring achievements. Getting convinced to join your online tutorials paid version. I hope they will be even better.
I like the way you explain. Really awesome!!! Thankyou.
Clear, concise, and informative. Thank you.
Hats off to Leila ! . I learns lot from your videos.
Glad to hear that
You're creative, so I've decided to outdo you in this field. تحية لك من المغرب
Excellent video. You are great instructor, and the video is top-notch in clarity.
Thursday is my favorite day! I was already wating for your new video. This is great, will practice this as you instructed. I want to be prepared for when the VBA course finally comes out.
Mine too! Great. Practice is always good :))
I did enroll in Udemy in Leila VBA course and I learned immensely. Thanks
Cells.Columns.Autofit is lovely.
Leila gets 🌟 🌟 🌟 🌟 🌟
Thankd leila
Back to our happy day
Waiting for the end to learn about the course
Good luck ur highness
You're welcome.
It's happy Thursday :) Wish you a great rest of the day.
Thanks Leila, nice preparation for the upcoming course
You're welcome Cobus & thank you.
was looking for multiple range syntax and found it here after looking on various sites. Thank you
You explain concepts very well and it is easy to follow. Thanks
Glad you think so!
Excellent way of explaining.. Thanks.
Leila you r awesome and nicely explains with smile and simple way. Thank u
Excellent video and narration! Thank you!
impressive teaching ability , right pace and right details . subscribed !.
Much appreciated!
Thank you for teaching the basics!!!!
Very nice presentation
Look forward to your course
You are an awesome teacher.
So Clear Explanation....!!! Makes understanding simple.! :) thank you So Much !! :)
You are welcome!
Such a smart, beautiful woman. Iran makes great things. Thank you for these videos.
@Elegant Fowl Pls
Used this techniq and solved a issue for which i am looking for a solution in long time. Keep rocking😎👏✌
Thank you! This video gave me an excellent explanation about cells and ranges referencing.
Glad it was helpful!
Brilliant. I like your videos Leila. Thanks a million.
Thank you! I’m glad to hear that.
Very clear, understandable. Great way to learn Excel m
I'm glad to hear that.
Outstanding presentation. Thank you.
Oh Wow, solved my problem in a few easy minutes, well explained.
I wish my school teachers, College Tutors etc, were like this.
Thanks for giving me some enjoyment with learning :)
I used this in Excel VBA "A1", "A" & lRow,
Where lRow was the last row number.
Cheers :)
Glad it helped!
So well explained, as always!
Very useful lecturer Leila , Thank you so much.
You're very welcome Sanjay.
Very good video. I liked the way you did the drag and drop with the named range...pretty cool way to do that.
Glad you like that :)
Awesome video. I think this should be the first video to watch for any one beginning to learn macros like me. I've spent hours trying to figure out how to refer cells in different scenarios.
Can you also mention how to refer to variable ranges? Eg Range from A1 to Ai where "i" is a result of another formula or loop counter etc.
Thanks for making a video on this topic though.
Great lecture, easy to follow.
Unbeatable knowledge Leila.. thanks..
Very much insightful.. No need to refer to any other material as far as Range object though VBA..👍👍👍
Thank You Leila for your time and cool video !
My pleasure Jossi. Glad you like the video.
Hello Leila the lesson to day was very clear. Keep it up.
Thank you Solomon. Will do my best :)
buenos principios excel, tienes un gran poder de enseñar, hay personas que creen que solo se debe enseñar lo mas astral, pero los principios de excel son excelentes, te felicito
Thank you Mario. Glad you like the video.
excellent review!
really awesome,,i acquired a lot of knowledge in your video.
So clear Leila! Thank you!
This was very helpful
Thanks a lot Madam, very inspiring, illuminating, impressive, inquisitive & revealing, . Explanation is superb, very articulate. On behalf of all my Excel learner friends A BIG SALUTE TO MY BELOVED BEAUTIFUL TEACHER.
Thank you very much Gopala for the kind words and for your support. I am glad you're enjoying the VBA series so far :)
Explanation is in very nice & systemic way, Thanks
Thanks and welcome
Amazing 👏 that was full comparhansive explanation 👏
I appreciate that efforts from your side
This is was very helpful
My pleasure 😊
This is fantastic. I will be taking the course at some point in the future.
Great! I hope you'll like it.
so well explained. Thank you
Awesome VBA Referencing video!!!! Quote Happy Fun : )
ExcelIsFun You should learn VBA as well😁
Yes but where could I find a good class!?!?! ; )
Thanks Mike - yes...... not easy to find a good class ;))
In the past week I've been locked up working on finalizing the course. I haven't had a chance to watch your videos, but I've added them all to my list. Have some catching up to do once I get out of here...
ExcelIsFun Try Wise Owl on youtube👍🏻
Thank you for reaching out to me!!! But I am sorry, antiksh... I was making a joke... I already know about Wise Owl. I feature the Wise Owl channel and Leila's channel at the excelisfun Channel home page : ) I was making a joke that I should take Leila's class. But really it is no joke - she is so amazing at teaching and marketing and making videos : ) Go Amazing Online Excel Team, including you antiksh!!!
Superb! Excellent!Thank YOU!
You are very welcome!
You are the best😊😊👌👌👌
Thanks Leila. Very Interesting. Some practice and will wait for the next one :)
You're welcome John. Agree - practice is always good :)
Great job, Leila!
Thank you! 😄
بهترین استادی👏👏👏
Thank you.
Thank You So Mutch.
This is a good hobby
Very clear and useful!
Thanks a lot, very clear!
thank you ; its useful
thanks, the many ways of getting ranges as part of a row or column is very helpful. all this is similar in openOffice too but they appear to use : and , differently and to also use ;.
in VBA i never access activesheet or activerange or selection, I actually pass a range or ranges to the code as in the following which i really have no error checking.
Function rms(ByVal Target As Range) As Double
'root mean squared. quadratic mean
Dim Cell As Range
Dim summand As Double
Dim tot As Integer
tot = 0
summand = 0#
For Each Cell In Target.Cells
If IsNumeric(Cell) Then
tot = tot + 1
summand = summand + Cell ^ 2
End If
Next Cell
rms = Math.Sqr(summand / tot)
End Function
i also use parallel ranges, either rows or columns 1 wide, but have to not use for each
Thanks a lot💕💕💕💕💕
Thanks a lot Leila.
You are Divine. Demi God of Excel
Useful video, Thanks
Bundle of Thanks Mam Nice Video
You're very welcome Khalid. Thank you for your support.
Hey Leila, thanks for your videos. I need to loop through a range (one column) and 1) check if the cell contains a specific text/string and 2) if true, write the text in a new cell (same row). Which of your tutorials do you recommend to watch? Also, I didn't find the next video/lecture you mentioned at the end of this one. thank you in advance
Tomorrow, if I remember, I’ll write the code here for you 🤜🏼🤛🏼
thank you very much
She is the best
Hi @Leila , how would you select two ranges and select them together ?
E.g. Header should be fixed Range (First Range) and second range would be from active cell range.
Just figured out my error: I named my module Cells which started this whole mess! (I left my question for anyone else's benefit)>>>>Thanks for the work you put into this video Leila, quick question: When I tried to use your second example: cells(1,1)="1st" it gave me an error: Compile error: Expected variable or procedure, not module. It's like vba isnt recognizing that expression because it's not even suggesting anything when I type the "cells(" part....... Thanks for any help.
mam i want to enter ony10 numbers in 12 cells in a row. 11th number could not be entered. please help me
I like how you teach. In fact, I'm enrolled in your Power Query Course. I'd assumed that you also have a Excel VBA course right? I'd like to learn macros.
Really awesome. Thanks.
Thank you, for sharing useful information.
Please can you advise how you open excel and vbe at the same time?
I snap the VBE window in place. You can use the shortcut key window and then right arrow. Or you can drag the VBE window with your mouse and then drag it to the right hand side until it snaps in.
I will try
Thank you
Thanks for the video ma'am.
You're very welcome Mohit.
Thanks Leila for such a wonderful video. However, when is the course coming on?
You're welcome Robert --- should be live next week :)
Can you make video on cutting length optimisation
U r the best
Very true
Thanks ma'am 😊 your videos are quite helpful
You're welcome Sami. Glad to hear that :)
Leila Gharani Ma'am do u have videos on data validation, concatenation ?
Very informative , but I have a request if you can show us how to merge cells than contains same value with option for Excel user to select the cells range as an input on dailog box
Thanks
Dear Leila, many thanks for your clear and advanced lessons taken here. Could you please let me know how can I write selective data records to a .txt file on disk and the ability to read a requested record from the .txt file on the disk back to excel with VBA.
Thank you dear.
Nice video
Thanks!
you are great, really it interesting video
Thank you! 😃
@@LeilaGharani
@@LeilaGharani dear I follow most your videos really it was very interesting and my level is progressing but can you guide me to advanced book in vba
Hi Leila, Your Tutorials are really Great and Helpful, Thanks so much for the great work you a doing here, I Need your Suggestion, I need support to fix one Run Time Error , Error Comes while copying PivotCharts or at the time of Pasting the Chart. as object Copy method failed or Object Paste Method Failed., I have Tried with Application.Wait function ,But it Required fixed time to wait, i wanted to make it in Conditional way, " if Chart object not Copied then Wait Scenario,.." if its copied then move to next code in VBA( My Macro Required many Graphs to copy and paste.. Thanks you in Advance..
thank you!
Hello Leila, thank you so much for all your video tutorial, they are all so useful for people like me. I started a new job and will like to automate one of the reports in excel using VBA. I know what I want it to do but can't seem to figure out how to run the VBA to do it. Is it ok to contact you for help? Please help me!