VBA code refering to columns by name
 

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 didnt 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("TripDa te: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 dont go as planned

Cells(iNextRow, 1) = TBDate.Value

Cells(iNextRow, 2) = CBCaptain.Valu

Cells(iNextRow, 3) = TBFirstOfficer.Valu

Thank
Ski

VBA code refering to columns by name
 

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

VBA code refering to columns by name
 

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

--
Message posted from http://www.ExcelForum.com