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

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

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.