How to use structured reference (ListColumns) in VBA

I want to do something simple...loop through a range retrieving values one at 
time and taking action on that value.  This range is an Excel 2007 table 
column.  Easy enough...BUT I want to use structured references to a table 
column by NAME, not numbers.  I don't want to define new names, just use the 
table column headings.   I've found ways of doing it with quotes, but that's 
not structured....the names in quotes do not change if the table heading is 
changed by the user.  

The formulas change if the table column heading is changed, but I want it to 
change in VBA too.  Can someone post a simple example that I can paste that  
will work?   Thanks!
0
Utf
3/15/2010 9:29:01 PM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
3093 Views

Similar Articles

[PageSpeed] 22

I don't really follow what you are attempting to do. Somewhere in all
this, there must be a literal constant against which a value can be
tested. The following might be what you're looking for, or at least
get you going in the right direction.

When you say that the column headings change, are all the names
preserved, just reordered, or are complete new names added
arbitrarily?

Suppose your data table is in cells C4:F8, where row 4 is the column
heading labels. The following code will scan down column C and if a 3
is found, it will return the data in that row in the column specified
by the FindHeader value. This value must be hard coded into the code.
How else, if not by number which you say you don't want to use, are
you going to identify the column from which the data is to be
retrieved.  I suppose you could prompt the user for the column
heading.  Replace

FindHeader = "research"

with 

FindHeader = InputBox("Enter a column name")


Sub AAA()
Dim TableStart As Range
Dim HeaderValues As Range
Dim NumColumns As Long
Dim FindHeader As String
Dim HeaderN As Long
Dim R As Range
Dim V As Variant

Set TableStart = Range("C4")
Set HeaderValues = Range(TableStart, TableStart.End(xlToRight))
Set R = TableStart(2, 1)
FindHeader = "research"
Do Until R.Value = vbNullString
    If R.Value = 3 Then
        HeaderN = Application.Match(FindHeader, HeaderValues, 0)
        V = R.Offset(0, HeaderN)
        MsgBox "Found: " & CStr(V) & " in column '" & FindHeader &
"'."
        Exit Do
    End If
    Set R = R(2, 1)
Loop
End Sub


Beyond this, I don't really understand what you are trying to do? How
do you identify the columns?

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
	Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com






On Mon, 15 Mar 2010 14:29:01 -0700, Carl S.
<CarlS@discussions.microsoft.com> wrote:

>I want to do something simple...loop through a range retrieving values one at 
>time and taking action on that value.  This range is an Excel 2007 table 
>column.  Easy enough...BUT I want to use structured references to a table 
>column by NAME, not numbers.  I don't want to define new names, just use the 
>table column headings.   I've found ways of doing it with quotes, but that's 
>not structured....the names in quotes do not change if the table heading is 
>changed by the user.  
>
>The formulas change if the table column heading is changed, but I want it to 
>change in VBA too.  Can someone post a simple example that I can paste that  
>will work?   Thanks!
0
Chip
3/15/2010 11:04:29 PM
Excel 2007 Tables
Example with ListObjects and ListColumns
Variable column headers.
http://c0718892.cdn.cloudfiles.rackspacecloud.com/03_16_10.xlsm
0
Herbert
3/16/2010 4:31:40 PM
Reply:

Similar Artilces:

help on adding a commandbar using VB com addin
Adding Commandbar to the New Mail message Inspector Window I was trying to add a new tool bar and also customize my new toolbar t automatically contain some commands programmatically. I wanna write COM add-in to OutLook that adds a new toolbar to the OutLook' Toolbar. This tool bar should apear only in new mail window. There are two commands(buttons) by name "Digitally sign Message" an "Encrypt Message Contents and Attachments". These are present in the Tools -> Customize -> Commands -> I categories select Standard -> on the Right side, if you scroll do...

Prefer how Outlook 2000 looked-now using 2003-Can I switch views?
I don't like the way Outlook 2003 displays and would prefer to go back to the 2000 display. Is this possible. Kinda, http://www.slipstick.com/emo/2003/up031029.htm#classicview might be helpful. "Manhart" <Manhart@discussions.microsoft.com> wrote in message news:A4549A3C-4B06-4AEC-8ADA-DD154CC7795B@microsoft.com... >I don't like the way Outlook 2003 displays and would prefer to go back to >the > 2000 display. Is this possible. Outlook 2003 is highly customizeable - tell us what you want to change, and we'll tell you how to achieve it. There is ...

Cell.Find in VBA
Hi, I have the following VBA Macro: Set FoundCell = .Cells.Find(What:="199", _ After:=.Cells(1), LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False) I don't how to write a VBA macro that would cells.find all 5-digit numbers. Here's an example: Column A 199 199 75781 75899 199 80012 Thanks, Hi I think you have to loop through all cells and check the length/value of each cell -- Regards Frank Kabel Frankfurt, Germany "Jeff" <Jeff@discussions.microsoft.com> schrieb im Newsbeitrag news:D3CEA329-2A...

Displaying a List of All VBA Procedures in Excel 2007 fromthe Ribb
How can I make this available to any workbook. For instance, I have a system running and it has around 280 modules. you might see the code at: http://msdn.microsoft.com/en-us/library/dd890502(office.11).aspx or bellow: Option Explicit Private Sub btnClose_Click() Unload Me End Sub Private Sub btnList_Click() ' Declare variables to access the Excel 2007 workbook. Dim objXLApp As Excel.Application Dim objXLWorkbooks As Excel.Workbooks Dim objXLABC As Excel.Workbook ' Declare variables to access the macros in the workbook. Dim VBAEditor ...

selecting rows using a variable
I am using excel 2003 and I need to select rows using a variable. Here is what I have now: Dim name As String name = Range("e800") ActiveWorkbook.Sheets("individual stats").Range("a1:a1540").Select Selection.EntireRow.Hidden = True Dim Start As Integer Dim finish As Integer Start = Application.WorksheetFunction.Lookup(name, Range("Q800:Q881"), Range("t800:t881")) finish = Application.WorksheetFunction.Lookup(name, Range("Q800:Q881"), Range("u800:u881")) ActiveWorkbook.Sheets("individual stats&q...

i am using trial version of outlook but it wont allow me to reply
i am using atrial version of outlook but when i try to reply to the email it wont let me, the reply word is not lit in the tool bar? Has your trial expired? Did you activate it? -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. ALWAYS post your Outlook version. How to ask a question: http://support.microsoft.com/KB/555375 After furious head scratching, terry mansfield asked: | i am using atrial version of outlook but when i try to reply to the | email it wont let me, the reply word is not lit in the tool bar? terry mans...

Using mixed Resource Types
With the company we have implemented EPM and infrequently the large complex plans fail to 'publish' into PWA. One of the causes thought is that these plans have both cost and work type resources within the same task. Is it good practice to separate these resource types into different tasks? -- joooles Hello joooles, It seems it's a bad pratice. Please see the posts I noticed several month ago: G�rard Ducouret ------------------------------------ If we assign a Cost resource to a task (as the only one resource on that task) and then we enter some % Complete,...

Linking using ranges?
I have a chart of data on one excel worksheet (and this chart has a constant number of columns, but the number of rows vary). I need to take all of the data on this chart (not all of the data on that worksheet) and put it in to the middle of another worksheet. I was wondering if there was a way to do this without using a macro. My thought was to try to create a range on each sheet and set one range equal to another, but apparently excel doesn't allow this (at least in version 2000, which I am using). Does anyone have any thoughts or ideas? If this isn't possible how woul...

How do you use the Livescribe smartpen with MS Office products?
I just received a Pulse Smartpen as a gift and I love it. I am curious about how other users of Office products utilize their smartpen features with Office ...

how can i format a date that uses "1st" or "2nd" or "3rd" or 4th".
how can i format a date that uses "1st" or "2nd" or "3rd" or 4th"... i am working on a program that generates certificates. i want to use dates such as "3rd of April 2010". when i run my program the words that appears onthe screen was 3-Apr-10. can you help me solve this problem? On Mon, 5 Apr 2010 20:47:01 -0700, cristin wrote: > how can i format a date that uses "1st" or "2nd" or "3rd" or 4th"... i am > working on a program that generates certificates. i want to use dates such > as &q...

Compacting
Windows XP Media Centre edition fully updated Compacting Outlook Express Before Compacting click on Local Folders Then Menu > File > Folder > Compact All Folders. Runs fine to Folders File then Warning/Error ?? Folders Fuile in use by Outlook Express or another application. Tried reboot then compact again before any other app sused problem persists. Any offers of help please -- Jim Bunton Is Fuile a user created folder or a typo? What is its size? Create a new folder. Move all the contents of the Fuile folder to it and delete the old one. If it is more ...

Copy/Paste VBA
Ich will eine ganze Visio Seite kopieren mit allen Verbindungen zwischen den Shape und deren Verklebungen (glue Ich habs versucht mit select all, group , copy paste und ungroup auch mit der Option bei copy/paste das es an der orignalposition positioniert werden sol abe 1) die Positionen stimmten nicht überei 2) die Verbindungen waren eingetragen aber nicht verglue Was nun einer eine Version die funktioniert und einfach is thx, mfg Daniel H. You might try Copy / Paste without doing the Group / Ungroup. Are you working with a specific set of shapes? Some Visio shapes have protections t...

VBA to count periods in a single cell
I am looking to count the number of periods within a single cell using vba. For instance if I have 4 cells that contain the following: 5M09-0000700.01.10 5M09-0000700.01.10.10 5M09-0000700.01.10.10.10 5M09-0000700.01.10.10.20 Thus the output of the code for the first cell should be '2', the second cell output would be '3', and the third/fourht cells would be '4' because that many period characters were encountered. Data within these cells will contain letters, numbers, periods, a few spaces and the "-" dash symbol. Any help is greatly appre...

Ho do you used Shared Calendar in Outlook with Roadrunner ISP
Hi all, I understand from my ISP that roadrunner is a POP3 server which should allow me to use my Outlook shared calendar with others in the office. However I am unable to use this Outlook feature. Does anyone know how to get around this problem? Many thanks. Kat "Kat" <designme21@yahoo.com> wrote in message news:epq238I0IHA.4004@TK2MSFTNGP03.phx.gbl... > Hi all, > > I understand from my ISP that roadrunner is a POP3 server which should > allow me to use my Outlook shared calendar with others in the office. > However I am unable to use this Outlook feat...

Using Min to Calculate across a table
I am attempting to massage data so I can see what the maximum number of pieces I can produce based off of various components. My query to figure out how many of each component is available per unit is complete and the output is: Part_Number, MaxComponent1, MaxComponent2,MaxComponent3, MaxComponent4 Does anyone have any suggestions as to how I could find the MIN of the components and group by part number? Cheers See the MinOfList() function here: http://allenbrowne.com/func-09.html A better solution would be to create a related table with many records for the combinations that are va...

Attn Jim Gordon: Requesting assistance with exporting OE 4.5 contacts for use in Entourage
Hi Jim, Paul Berkowitz suggested you might be willing to assist me with a dilemma. I have some old mail and contacts in an OE User(s) folder from OE 4.5 that I need to import into Entourage X. I am on a machine that is not OS9-bootable and I cannot get OE 4.5 to launch under Classic mode in Panther. OE 5 loads fine in Classic, but 4.5 gives a shared library error. I don't have an OE 4.5 installer anywhere I can find, so I can't try a reinstall under Classic. I finally found a way to get the mail into Entourage, after much trial and error with every email program I could find, since ...

Attn: M02,3,4 users who use Passport/WinLiveID
You will get broken at the end of July, 2008. (That's about 45 days from now.) See http://support.microsoft.com/kb/894020. You should remove the Passport/WinLiveID from your file before that time. (File|Password Manager). There have been many indications this day would come. Now you all have a date certain. Does this also means backup files taken from M02,3,4 will be inaccessible, even if they are opened with a current version of Money? Although if you have to go back four, five or six years you probably have bigger issues. But I have at times opened older backup files, even five...

Solver VBA
Currently I have a simple solver VBA script in my excel workbook. The script sets the value of cell "I1" to 1 by changing cell "H1", as shown below: Sub repeatsolve() ' SolverOk SetCell:="$I$1", MaxMinVal:=3, ValueOf:="1", ByChange:="$H$1" SolverSolve End Sub What I would like to do is set the value of all cells in the "I" column, of a specific range, to 1 by changing its respective cell in column "H". Does anyone have any suggestions? Thank in advance. -- kwrohde --------------------------------------------------...

using stock
hello, i have been using retail management for about 3 weeks now. but i can't figure one thing out.... we use around 30 suppliers for our dollar store. and we also use our basement + back room as our stock room. in the basement as well as back room we have shelves with numbers on it. i want to know, how i can tell the software that I have 24 light bulbs in shelf 5 in the stock room so don't put that item on the purchase order or dont let any one order it. we would like to get the 24 light bulbs out of stock add them to the store inventory and let them sell out 1st. i have tr...

How can I choose which lines are frozen using "freeze pane"?
I've tried highlighting the row I would like to freeze, usually just the first row, but it always freezes it after the 16th row. Any suggestions!? This is really frustrating! See if this old post helps: http://tinyurl.com/2qdzt -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "emilyp" <emilyp@discussions.microsoft.com> wrote in message news:35457940-EC4E-4071-B8C5-4AFD3DABDCB6@microsoft.com... I've tried highlighting the row I would like...

Workbook minimizes when sending as attachment using Outlook Expres
When I send a workbook as an attachment, using Outlook express, the Excel document minimizes instead of staying open. How do I stop this so the document will stay maximized? ...

Removing at item using qsrules
I am trying to remove an item that is on the pos store operations screen. I am using the following code and it is giving me a "Type mismatch" error: Dim objDeleted As Object Set objDeleted = Session.Transaction.Entries.Remove(Session.Transaction.Entries(1)) I have also tried to define objDeleted as a transactionentry & transactionentires and take off the "Set" and I get "Object variable or With block variable not set" error. Has anyone been successful in removing an item and if so could you provide the code snippit? Thanks, Robert -- rshuptrine armsys ...

How to delete most recently used file list
Can someone please tell me how to delete a file from the most recently used file list in Money 2003? The file is already physically deleted so I don't want it in the listing. I am talking about the "File" pull- down list. Thanks!! On Feb 6, 3:50=A0pm, bajpd <ba...@peak.org> wrote: > Can someone please tell me how to delete a file from the most recently > used file list in Money 2003? =A0The file is already physically deleted > so I don't want it in the listing. I am talking about the "File" pull- > down list. Thanks!! Nevermind, i figu...

Use jpg as border
Hi - I have a specific jpg that I wish to use as a border on a Word 2007 document. This jpg will be 3/4 point, and will border the entire document with multiple copies of the picture. In other words, the picture will be copied around the entire border of the document. I looked in Word 2007 'Help' but could not find the info on how to do this, if indeed it is possible. Any assistance will be greatly appreciated. Thanks Joe Hello Joe, if your jpg file is of right dimensions, you can use it as a background image. Therefore you can have it as border as...

Help using lookup function
I'm having trouble using the lookup function. I have numbers that look like this in a list, although it's only part of what I'm using: -11 2179 -10 2420 -9 2661 -8 2897 -7 3123 -6 3332 -5 3521 -4 3683 -3 3814 -2 3910 -1 3970 0 3989 1 3970 2 3910 3 3814 4 3683 5 3521 6 3332 7 3123 8 2897 9 2661 10 2420 11 2179 When I use the lookup function on another sheet of the cell, it does not look up the proper value always. My columns go from (-100, 100). Outside the values of (-10,10), the lookup works properly. These are the lookup cells: -11 2179 -10 2420 -9 2420 -8 2897 -7 3123 ...