After running the code on the last row the output will look like this. You can easily avoid the use of ActiveCell here, by using the input box to get the stock name / row number from the user. offset of zero rows and three columnsĪctiveCell.offset(0, 3).Interior.Color = vbRedĪctiveCell.offset(0, 3).Interior.Color = vbGreenįor further details on the Offset Method, please refer to the article “ How to use VBA Range.Offset Method” 'Set value and format of the forth column i.e. This will change the value of the cell two cells below, and three cells to the right of A1 to 4. The basic syntax looks like this: Range(A1).Offset(2,3).Value 4. Another way to select a range is to use the Offset method. offset of zero rows and two columnsĬurrPrice = ActiveCell.offset(0, 2).Value Using Excel 2007 This course will get you up to speed on using VBA in Excel 2007. There’s also another method which you can use to activate a cell. To get the values of the Previous Close and Current Price we will use the Offset Method Sub offset()ĭim preClose As Double, currPrice As Double This code tells VBA to select the cell A5 and if you want to select a range of cells then you just need to refer to that range and simply add. Here, we will assume that the stock name is selected before running the macro. The desired output is that in the column “Gaining”, Y or N should be inputted based on the price level and the background color should be set to Green or Red respectively. Say, in your Excel sheet you have multiple lines of data as shown below and you need to process data only for a single selected row. Now let us look at a more practical example of using ActiveCell. The current region can be useful when the range of data is not fixed and you need to perform certain operations on it, like format it, copy it, insert charts or send an email using that range. 'Paste the current region in another sheet at the same locationĬurReg.Copy Worksheets("Sheet2").Range() 'Get the current region and assign it to a Range variableĪ(XlDirection.xlUp).SelectĪ(XlDirection.xlToLeft).Select The entire range is copied to another sheet at the same location. In the following example, the selection is expanded to include the cells adjoining the active cell that contain data and the background color of the current region is modified. The CurrentRegion property returns a range of cells bounded by blank rows and columns. ThisWorkbook.ActivateĪ(XlDirection.xlDown).SelectĪfter the execution of the code, the last cell will be selected like thisĮxample 5: Selecting the cells that contain data around the ActiveCell using CurrentRegion If you want to select the last cell that has data in this active column, you can use the code snippet below.
0 kommentar(er)
