Skip

an example:

Sub SkipCodeTest()
' set up some names ...
' column J
ActiveWorkbook.Names.Add Name:="SkipJ", RefersToR1C1:="=Sheet6!C10"
' and column L
ActiveWorkbook.Names.Add Name:="SkipL", RefersToR1C1:="=Sheet6!C12"

Dim RowJ As Long
Dim RowL As Long
Dim ColJ As Long
Dim ColL As Long
Dim Cell As Range

ColJ = Range("SkipJ").Column ' establish the column no for Column "J"
ColL = Range("SkipL").Column ' establish the column no for Column "L"

'Process each cell in column "J"
For Each Cell In Intersect(Range("SkipJ"), Range("A1:IV20"))
RowJ = Cell.Row
' display the equivalent address in column "L"
MsgBox Cells(RowJ, ColL).Address(False, False)
Next 'Cell
End Sub

Run the code ONCE to establish the names and display the addresses. Then
comment out the ActiveWorkbook.Names.Add lines. Insert a couple of columns
between columns J and L and run the code again. The addresses displayed
should reflect the number of columns you have inserted. Hence you can
protect your code from anyone adding or deleting columns. They can still
screw it up if they delete the named column or the name itself but ...

Modify to meet your requirements

Regards

Trevor


"SkipAtPNS" wrote in message
...
I am having a bad time incorporating column names in my code. I have
named the columns in the spreadsheet. When I attempt to reference those
column names in my code I get error codes of data type mismatch. Am using
2003.

It worked great in the WorksheetFunction
CountA(Range("TripDate:TripDate"))

I changed TBDate.Value to TBDate..text, that didn't work. Formatted the
column in several different formats, none worked.

Can someone tell me how to refer to columns by name in Cells(Row variable,
Column Name)???

The procedure I am trying to write is as follows;

Dim iNextRow As Integer

iNextRow = 0

' Next row in sequence to accept new data.

XXX NOTE: This statement worked fine.
iNextRow =
Application.WorksheetFunction.CountA(Range("TripDa te:TripDate")) + 2

XXX NOTE: I want to refer to column 1 by the name assigned to it
"TripDate". When I do that I get a data type mismatch error. None of the
below work.

Cells(iNextRow, "TripDate") = TBDate.Value This produces an error.

Cells(iNextRow, "TripDate:TripDate") = TBDate.Value This produces an
error.

XXX Obviously the below works, however if the user adds a column things
don't go as planned.

Cells(iNextRow, 1) = TBDate.Value

Cells(iNextRow, 2) = CBCaptain.Value

Cells(iNextRow, 3) = TBFirstOfficer.Value

Thanks
Skip