Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Accessing Columns by name in a Row loop in VBA
I have a sheet with columns named that I want to access in code. And the
area I want to acees is also named. I want to be able to write code like this (which is pseudo code of course): Dim table As Range table = Sheets("Books").Range("OutOfPrint") Dim row As Range For Each row In table Dim title As String Set title = row.Column("Title") Dim cost As Integer Set cost = row.Column("Cost") Next row The bit I don't get is how I can do the Column("<Name") magic. Any ideas? Thanks, Tyler |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Accessing Columns by name in a Row loop in VBA
Here is a general purpose function you can use:
Public Function FindCell(FindRange As Range, FindRow As Variant, FindColumn As Variant, _ Optional UseColumn1 As Boolean = True, Optional UseColumnHeader As Boolean = True) As Variant ' Returns a cell's contents by specifying row and column ' Row and column can "name" (value in 1st column/top row) or number ' UseColumn1 and UseColumnHeader determine if search is by matching the name (true) or number (false) Dim FRow As Integer, FColumn As Integer On Error GoTo NotFound With FindRange If UseColumn1 Then FRow = WorksheetFunction.Match(FindRow, .Columns(1), 0) _ Else FRow = FindRow If UseColumnHeader Then FColumn = WorksheetFunction.Match(FindColumn, ..Rows(1), 0) _ Else FColumn = FindColumn FindCell = .Cells(FRow, FColumn) End With Exit Function NotFound: FindCell = CVErr(9001) End Function So, for example, FindCell(ActiveSheet.Cells, 5, "NAME", False, True) will return the value in row 5 of the range that is in the column with the header "NAME" By the way, it is good practice to avoid using words like "row" and "column" as variable names: since there are object properties with these names you are bound to confuse yourself - or, worse, confuse VBA. -- - K Dales "Tyler W. Wilson" wrote: I have a sheet with columns named that I want to access in code. And the area I want to acees is also named. I want to be able to write code like this (which is pseudo code of course): Dim table As Range table = Sheets("Books").Range("OutOfPrint") Dim row As Range For Each row In table Dim title As String Set title = row.Column("Title") Dim cost As Integer Set cost = row.Column("Cost") Next row The bit I don't get is how I can do the Column("<Name") magic. Any ideas? Thanks, Tyler |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Accessing Columns by name in a Row loop in VBA
Mmm, I guess I was hoping for a slightly simpler solution. There is no
way, given a range which represents a row, to get a cell based on its column name? Thanks, Tyler K Dales wrote: Here is a general purpose function you can use: Public Function FindCell(FindRange As Range, FindRow As Variant, FindColumn As Variant, _ Optional UseColumn1 As Boolean = True, Optional UseColumnHeader As Boolean = True) As Variant ' Returns a cell's contents by specifying row and column ' Row and column can "name" (value in 1st column/top row) or number ' UseColumn1 and UseColumnHeader determine if search is by matching the name (true) or number (false) Dim FRow As Integer, FColumn As Integer On Error GoTo NotFound With FindRange If UseColumn1 Then FRow = WorksheetFunction.Match(FindRow, .Columns(1), 0) _ Else FRow = FindRow If UseColumnHeader Then FColumn = WorksheetFunction.Match(FindColumn, .Rows(1), 0) _ Else FColumn = FindColumn FindCell = .Cells(FRow, FColumn) End With Exit Function NotFound: FindCell = CVErr(9001) End Function So, for example, FindCell(ActiveSheet.Cells, 5, "NAME", False, True) will return the value in row 5 of the range that is in the column with the header "NAME" By the way, it is good practice to avoid using words like "row" and "column" as variable names: since there are object properties with these names you are bound to confuse yourself - or, worse, confuse VBA. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
loop over columns | Excel Discussion (Misc queries) | |||
incrementing columns in a loop | Excel Programming | |||
Loop through columns and then rows | Excel Programming | |||
loop through columns | Excel Programming | |||
Loop 20 columns Help! | Excel Programming |