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".
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 :-)
@@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.
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 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.
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 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.
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
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.
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.
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!
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 :-)
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...
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!!!
Hello Leila, My question is Steel sales Table Product|Qty|Wgt|Rate|Unit|Amount SteelTm.10. 350. 49. 1KG. 16660 Pipe Ms.10. 00. 980. Per . 9800 B.Wire . 2. 00. 1024 per. 2048 SS Pipe. 7. 453.350.10KG 158550 Sq Tmt. 1. 100 2500.100KG 2500 Means when I go to UNITS their will be a options ComboBox TO Select any One of this. To Multiple with Rate 1.PER 2.10KG 3.1KG 4.100KG When I Select PER It should Multiply with Rate * Qty Ok When I select 10kg/100kg/1kg It should Multiply with Rate * Weight Please show me this Userform Vba code
Hi Leila! How can I used the same macro in one excel file for the next column without changing the numbers of the other columns while I m using the same spreadsheet day by day. Let’s says the column are for day 1,2,3 and so one.
hi, your video is really great! I want to write a text every open sheets and for example there are 100 tabs open and I want to every A1 cells... Is there any way for this? thank you.
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.
Your videos are very informative. How do I reference entire rows as variables? For example if I am using them in a For/Next loop, instead of typing Rows("9:9").select? I can't figure out how to put a variable J in place of the 9's. 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"
hi, all your videos are very much useful. i am looking solution for following problem, cell A1 contain value of 1 cell A2 contain value of 2 cell A3 contain formula which is =A1+A2 hence the answer is 3. i want the breakup of answer 3 which is 1+2 in cell A4. so i write the formula in cell A4 is =A1&"+"&A2. Every time i have to rewrite the formula in cell A4 when i change the formula in cell A3. Please provide the solution for this in simple manner.
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..
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
Hello Leila sorry to bother but the following command doesn't work for me : Range(“A” & 6,”C” & 6)= “6th” I got run time error 1004. What i am doing wrong ? Thanks for your help
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.
Hello mam, I have a query... I have a fixed no in four cell range. Suppose 20 in my 1 cell.. When i input 2 below 20 it becomes 18. Again below 2 input 5 it becomes 13 ans so on without interrupt 20 no.Kindly help on this
Not sure if you offer help to folks but here goes. I have a range of values I created a dynamic distribution chart for that updates as I enter values, that range values from 2 through 12. The chart gives me the total number of occurrences each time any of the values appear. The total number of rows with these values is 5000. I want to create a supplemental chart that tracks just the last 50 entries in row order as data is entered. This supplemental chart will always give me the last 50 rows of data. Is the presentation posted here the best method to achieve those results? Thanks for any advice. Great value here.
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 :)
Hi, A query abt adjusting the first column to the third column using columns properties Range(Columns(1),Columns(3)).ColumnWidth = 5 This adjusts the first column to the third column How to adjust only column 1 and column 3 using columns() command . Thank u .
Hey, maby Are you know, how can I use the Filter Function, but I want to fix one Cell of description, and this description I have to show always, if I use the filter function?
How can I assign values that includes the formats like if it is bold? For example cells(1,2).value=cells(1,1).value but it must include the bold format from cells(1,1).
Hi Can you help me? instead of Range("f4").Cells(2, 2) = "1st" can I substitute . Cells(2,2) for Cells (D4,2) if the value of D4 = 2. I want to do this as I can use a counter to change the value of D4.
Hi, from your example: Range("A" & 6, "C" & 6 ), Can I select multiple cells but using cells(r,c) example cells(1,1) and cells(1,6) . I want to select only cells 1,1 and cells(1,6). how to do it?
How do you reference named ranges that's not in the active sheet? I tried putting sheets("sheetReferenece") before the range method but I always get a "Application-defined or object-defined error".
Hi Leila till now I have seen 11 videos there has not been further addition in this VBA series. Will their be any further addition of videos in this series?
how to write in the cells put the column value through vba userform. example A1=1001,A2=140,A3=ABCD, A4=MM/DD/YYYY B1=1002, same as above mentioned A2 A3 A4.............etc... can you explain as well add, update & delete button. I hope you.
Hello Leila Gharani, I am create a dashboard with excel 2019 but when I open it with excel 2016 than its good but after many days when I reopen in excel 2019 than all objects are scale-up & displaced. I am so disappointed. Please help me with this error, please.
Hello Leila mam, i have merged a 4 quarter data as a single yr datasheet and know using name range, i would like to name as Q_1 and so on and using dropdown in top most cell i would like to go through quarters. how should i do that?
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.
Hi, Could you help with a specific problem? In VBA, I have a function in sheet 1 using calulating using values on sheet 2. To do so, a defined ranges in sheet 2 in which the values will be picked. In a sub, to do so, I have to activate the sheet 2 where the ranges are to declare each of them. But, in a function, it seems, there is no proper solution since the worksheets("").activate is not read. Do you have a solution to help me finish my function F called in "Sheet2": Function F() As Integer Dim r As Range Set r = Worksheets("Sheet1").Range(Cells(1, 1), Cells(1, 3)) F= r.Cells(1, 2).Value End Function
Is there a way to set one of the reference values in the cells function to a variable? In python if you have an array, you can do something like this. array1 = [1,2,3,4,5] b = 0 while(b < len(array1)): print(array1[b]) b = b + 1 Is it possible to do something similar with the cells function? So something like MsgBox(cells(1&b))....I keep trying it and it is not working for me...
Leila, thanks for the video, but unfortunately it is pretty useless as you are dealing with little amount of cells here. Now imagine that I have 2000 cells of data that I need to divide into tables after each 16 rows, and give name to each table, i.e., A1, A2, A3, A4, A5, A6 etc. . If I am going to do everything by your tutorial; it will take me an entire day entering everything manually. Therefore, it would be amazing if you could include some useful macros that let you work easily with big amount of data.
Hi - the aim of this lecture is to learn how to reference a range. Just like in Excel we need to understand how to input data in cells. There is so much more to VBA than this lecture. Knowing how do this step is the building block of what is to come. It's one of the beginning lectures in the 22 hour course.
@@LeilaGharani Would be cool to see some fancy programming macros with different variety of loops and "if, then" clauses. As you have said, there's a lot in VBA. Unfortunately, there's absolutely no videos in youtube with programming algorithms :(
Good one, i wanted to ask you a question but its not related to this video, I was searching a strategy, where if I type some thing say a "Yes" in column I, and in row numbr 6 i want my work book to show all Hidden comment boxes, it has in row number 6 as and when i type a yes in column I , (this is not restricted to a particular row, i want to make a dynamic One)
