Excel VBA - Cell Referencing

in this video we'll see how to refer a cell from VBA macro suppose I write a piece of code into the VBA Visual Basic for application and I want to access this cell from this place in the VBA itself I want to write some data from VBA into this cells how do I do that so let us see that now I will be working in the same workbook that is Excel VBA I have created a sheet named as cell referencing and now I will create a macro here so you observe here I'll go to Developer tab and click on this Visual Basic okay early in the previous video we have recorded two videos for recording and writing a simple macro now let us write a third macro for accessing or you can say cell referencing so what I'll do is I will create a module here I will write all the all the code part into this module so I will go to the insert click on this module okay the name is given as module 1 I will change the name here if I want to change the name I can go to this view properties window okay and here I can just change the name I can write here as C underscore cell referencing cell referencing okay so I've written here if you see the name a change C underscore cell referencing now I create a macro starting with the sub procedure so I'll tie a pair as sub I already - cell underscore referencing okay now there are four basically methods to access the cell if you want to write some data into your Excel cells there are four methods let us start the first method now if you observe my cursor is in the cell number b3 if I want to write some data into my active cell my active cell is right now b3 if you see here it is given as b3 how do I write it so I'll just open that macro here again okay and I will just type here as active cell dot value okay the moment you type active cell and you put here a dot you will get all the methods related to that active cell okay so active cell dot value because I want to insert some value I will write in double quotes if it is a text you have to write in double quotes tutorials okay that's it so active cell dot value equal to two tools if there is a number you do not have to write anything here double quotes again I'll write as active cell dot value equal to 40 okay now let us see that first it should type as two tools and second it should type as 40 if I click on the Run button if you see here it is got as 40 now which value it accepted first one or second one the very first thing is it accepted the first value then it accepted the second value now if I want to understand step-by-step execution if you want to understand step by step execution do not use this run okay you have to use the debug menu bar okay or you have to you step into from where you'll get it now if you observe here I have a toolbar which is given here I will just bring it down yeah this is a debug toolbar if you don't have this toolbar suppose you do not have it you can go to this view menu bar you can go to this toolbar and you can click on debug okay so that is the debug toolbar now what will happen is if you want to see step by step execution you have to just click on this button the name of this button is step into I will show you this button that is step into step into will execute your commands or your statements step by step so you observe here when I click on this step into or if I press f8 from my keyboard I'll just click on step into now that's the first cell which is it is working right now step into second cell now this cell it will be this line it will be executing again if I click on this place you see this line has been executed and you can see the data which is given as two tools now if I execute this line if I press step into step into now if you see the value a change to 40 so if you want to see step by step execution of all the steps use this step into or you can just use f8 from a keyboard here onwards I will be using the shortcut s f8 to go by step by step so I will just take this toolbar back again okay and I will just properly shift yep this is so I will just press f8 again to finish the execution part right so I hope you understood that is the first method to to know give some value to some particular cell let us see the second method if you want to add some value to particular cell which is not an active cell let's say suppose cell number b5 how do you add it so you can just see you now I will type here as bracket b5 dot value equal to 70 okay so b5 in the brackets I have given the cell address dot value equal to 70 if you want to execute press f8 from a keyboard I will press f8 f8 after this line you see this is executed two tools again if I execute you see this given as 40 again if I execute it is given as 70 now if you see execute see this 70 the cell number b5 is given as 70 so that's the second method if you want to add some data but suppose you do not want only one cell you want multiple cells let's say you want c1 to c10 all the areas you want with that value so second method you can use brackets b1 or you can see as c1 to c10 dot or you can just simply write as tutorials right so what I've done is c1 to c10 I have given the entire range equal to in brackets I have given the value again I'll press f8 from a keyboard you see first send in seconds and a third sentence and fourth statement if you see if this line crosses c1 to c10 we'll have all the values ready to tootles you can see c1 to c10 all have the value says tutorials so these are these are the second method that was the first method second method let's see the third method if you want to add some values into other areas let's see the third method I'll type here as cell okay when I type cells if I open the bracket first you have to give the row number and second you have to give the column number now if I say I want to give the row number as 3 and column number as 2 so row number is 3 and column bura or 2 which is already taken so I will say row number 8 and column number two this one row number 8 column number 2 so I will just go back to this macro here again okay and here I will type as row number 8 comma column number 2 okay so cell number it will be as b8 the value will be given in this b8 if I type here as dot value and if I write here as India okay so this cell will contain this cell will contain this value that is India let us see that we will see that if I press f8 from a keyboard this will work second third fourth and let's see that this one if I press here I should get in this area that is B this value if I type here now if you see I have got the text here's India in this please okay that is how it is working on so that's the third matter let's see the fourth and the last method which is widely used all across our coding part ok these this all are used rarely but let's see the next one which is used widely across ok I will show you that method I'll type as range and I will type as a 1 dot value equal to Mumbai some textile right here Mumbai that's the name of the city in the India so range bracket a1 dot value equal to Mumbai or else if I want to write some more values I'll write as a 2 to 8 n equal to Jakarta okay so this way so this two are the same that is for one cell that is for the entire range so you can select any one like this so if I press here as f8 from my keyboard f8f8f8 and so on this is working this is working this one let's see what happens range a1 dot value now in this cell if you see a 1 there should be a mumbai value now this value is not visible because a2 is visible right now so i will just go back you see there's a mumbai which is given him i will go back to that area alt tab is a shortcut alt app to go to that window again now the next one is a 2 to 8 n equal to jakarta so a 2 to a 10 if you observe here again I will press f8 from my keyboard now you see a 2 to a 10 all the cells is having the value as Jakarta right so this is one method again right f8 finally so I hope you are understood there are different methods to refer a cell you can use any of the methods but widely used are these 2 methods this is the first one first most used method and second one is cells 8 comma 2 this is used for continuous for loop if loop and if condition in all so make sure you remember these two things these are rarely used a lot right so I hope you able to understand how to refer cells or how to write the values into your excel sheet cells that's all for this video

Channel: Tutorials Point (India) Ltd.

Views: 404,746

Rating: 4.9108634 out of 5

Keywords: Excel VBA - Cell Referencing, VBA, Marcos, Microsoft, Excel VBA, Cell Referencing

Id: IzE9GDf1VGE

Length: 8min 32sec (512 seconds)

Published: Tue Dec 06 2016