Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
refering to other workbooks | Excel Discussion (Misc queries) | |||
Refering to other tabs easily | Excel Discussion (Misc queries) | |||
Refering a cell | Excel Worksheet Functions | |||
Refering to Cells | Excel Worksheet Functions | |||
Refering to Cell's Name in Macro | Excel Programming |