VBA code refering to columns by name

[Guest]

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 Intege

iNextRow =

' Next row in sequence to accept new data

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

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.Valu

Cells(iNextRow, 3) = TBFirstOfficer.Valu

Thank
Ski

 

[Trevor Shuttleworth]

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

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("TripDate: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.

 

[teatree]

after testing this, it seems that if you name an entire column, ie G:G
you can select the column by "Name," and you can even select it b
"Name:Name" but "Name" & "21" to get cell G21 won't work, because yo
are essentially asking for the entire column G and tacking a 21 on th
end.

However you could name cell "G1" and then offset by the number of row
you'd like...

Range("TripDate").Offset(iNextRow-1, 0) = TBDate.Value

however, there are wiser heads than mine out here, perhaps someone els
can make it work with the named column... i'd be interested to see tha
work..