Description:
Its one of my first questions when I started learning EXcel VBA, How to Read or Get Data from Worksheet Cell to VBA? To automate any thing in excel, first we need to read the data in Worksheet and based on that we can execute the next steps.
Read or Get Data from Worksheet Cell to VBA in Excel – Solution(s):
It is very simple to read the data from Excel to VBA. We can use Cell or Range Object to refer a Worksheet Cell.
Get Data from Worksheet Cell – An Example of using Cell Object
The following example will show you how to read or get the data from Worksheet Cell using Cell Object.
Example Codes
In this example I am reading the data from first Cell of the worksheet.
Sub sbGetCellData() MsgBox Cells(1, 1) 'Here the first value is Row Value and the second one is column value 'Cells(1, 1) means first row first column End Sub
In this example I am reading the data from first row and fourth column of the worksheet.
Sub sbGetCellData1() MsgBox Cells(1, 4) End Sub
Here is sample picture, which helps you to understand this concepts. Any Row or Column number start with 1 and You have to specify Cell(RowNumber,ColumnNumber) to read the data from a Cell of the Worksheet.
Get Data from Worksheet Cell – An Example of using Range Object
The following example will show you how to read or get the data from Worksheet Cell or Range using Range Object.
Example Codes
In this example I am reading the data from first Cell of the worksheet.
Sub sbGetCellData2()
MsgBox Range("A1")
'Here you have to specify the Cell Name which you want to read - A is the Column and 1 is the Row
End Sub
Here is sample picture, which helps you to understand this concepts. If you select any cell in the worksheet, you can see the name of that cell in the Name Box.
Get Data from Worksheet Cell – Specifying the Parent Objects
When you are reading using Cell or Range object, it will read the data from Active Sheet. If you want to read the data from another sheet, you have to mention the sheet name while reading the data.
The below example is reading the data from Range A5 of Sheet2:
Sub sbGetCellData2()
MsgBox Sheets("Sheet2").Range("A5")
'Here the left side part is sheets to refer and the right side part is the range to read.
End Sub
In the same way you can mention the workbook name, if you are reading the data from different workbooks.
Premium Project Management Templates
50+ Professional Project Management Templates!
A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.
LIMITED TIME OFFER
50+ Project Management Templates Pack
Excel PowerPoint Word
Ultimate Project Management Template – Advanced
Excel Template
ULTIMATE PROJECT MANAGEMENT TEMPLATE
Excel Template
20+ Excel Project Management Pack
Excel Templates
20+ PowerPoint Project Management Pack
PowerPoint Templates
10+ MS Word Project Management Pack
Word Templates
I want continue number from A1 to A10 but only while am typed in B1 to B10 like
A1 1 Magesh
A2 2 Kumar
A3 3 Sathish
A4
A5 4 Raja
A6 5 Ram
A7 6 Raju
A8 7 Samy
A9 8 Prabu
A10 9 Raja
A11 10 Magesh
how to put this number alternately in Ms Excel 2007 using VBA.
with regards,
Magesh
Hi Magesh,
You can use the following code. Paste this code in your worksheet module:
Private Sub Worksheet_Change(ByVal Target As Range)
'print if B is not empty and A is empty
If Cells(Target.Row, 2) <> "" And Cells(Target.Row, 1) = "" Then
Cells(Target.Row, 1) = Target.Row
End If
End Sub
Thanks-PNRao!
i have excel sheet need to create sub menus , when select value its creates sub menu , when select value its generate another menu with formulas & conditions