Referencing cells using named columns in Exce

Microsoft Excel 2003 (full product)
December 23, 2010 at 09:26:37
Specs: Windows XP
I am new to VB and am trying to write a generic macro that create a flatfile from a worksheet for a all rows but only for specific columns. The workbook has 25 worksheets where the column "Name" might be in different column (ex. A or B or C). I was hoping to name the columns and then reference the cell value using that name but can not seem to figure out the syntac. Has anyone done this before?

See More: Referencing cells using named columns in Exce

Report •

#1
December 23, 2010 at 13:48:36
re: "I was hoping to name the columns and then reference the cell value using that name"

When you say "name the column" I assume you mean a Named Range and not just a column label in Row 1.

This code will loop through all the Named Ranges in a workbook and return the Column number for the column that is named Name. In my example below, I'm simply presenting a Message Box with the column number.

If I needed to reference a cell, I would use the colNum variable in something like:

Cells(1, colNum)

Sub NamedColumnNumber()
Set nms = ActiveWorkbook.Names
  For r = 1 To nms.Count
    If nms(r).Name = "Name" Then _
      colNum = nms(r).RefersToRange.Column
      Exit For
  Next
  MsgBox colNum
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
December 23, 2010 at 14:15:05
Thanks!

How do I define nms?


Report •

#3
December 23, 2010 at 14:29:32
I just realized it needs to be a Names.

Thanks the loop worked great!!!


Report •
Related Solutions


Ask Question