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?
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 SubClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.
Thanks! How do I define nms?
I just realized it needs to be a Names. Thanks the loop worked great!!!
Ad Choices