Return nth column number in a range
This tutorial shows how to return the nth column number from a selected range using an Excel formula or VBA
Example: Return nth column number in a range
|
=COLUMN(B5:D10)+F5-1
|
This formula uses the Excel COLUMN function to return the column number of the first cell in the selected range and then adds the nth column number (subtracting one) that you want to return. In this example we are returning the second column in the range which returns a value of 3 (Column C).
|
Sub Return_nth_column_number_in_range()
'declare variables
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'return the nth column number in a range
ws.Range("G5") = ws.Range("B5:D10").Column+ws.Range("F5")-1
ws.Range("G5") = ws.Range("B5:D10").Column+ws.Range("F5")-1
End Sub
EXPLANATION
This tutorial shows how to return the nth column number from a selected range through the use of an Excel formula or VBA.
Using the COLUMN function in both the Excel and VBA methods it will return the first column number in a selected range. You then add the nth column number and subtract one.
FORMULA
=COLUMN(range)+column_number-1
=COLUMN(range)+column_number-1
ARGUMENTS
range: A range of cells for which you want to return the nth column number.
column_number: The nth column number that you want to return.
range: A range of cells for which you want to return the nth column number.
column_number: The nth column number that you want to return.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Return column number of a specific value | How to return a column number of a specific value using Excel and VBA | |
| Return column name of a specific value | How to return a column name of a specific value using Excel and VBA | |
| Return column name of a specific cell | How to return a column name of a specific cell using Excel and VBA | |
| Return first column number in a range | How to return the first column number from a selected range using Excel and VBA | |
| Return first column name in a range | How to return the first column name from a selected range using Excel and VBA |
RELATED FUNCTIONS
| Related Functions | Description | Related Functions and Description |
|---|---|---|
| COLUMN Function | The Excel COLUMN function returns the first column number of the selected reference |