using the offset with a cell name??

[cparsons]

I have a spreadsheet that I use as input to my macro. The informatio
on the spreadsheet remains the same but the columns that the data is i
can change. (i.e. Today the first name is in column a but tomorrow th
name could be in column C) I have a total of 34 columns on th
spreadsheet whose columns could fall anywhere on the spreadsheet.

If I name the header cell of the first name column "FNAME" can
somehow use "FNAME" in a loop? Same with the other 34 columns.


For x = 0 To Total_Name_Cnt - 1
First_Name(x) = .Offset(x, 'FNAME')
Last_Name(x) = .Offset(x, 'LNAME')
SSN(x) = .Offset(x, 'SSN')
Phone(x) = .Offset(x, 'PHONE')
Address(x) = .Offset(x, 'ADD')
Zip(x) = .Offset(x, 'ZIP')

.... etc

Next x


This example didn't work but I was looking for an alternate to doin
this.
I realize that I can perform a loop and then within the loop I ca
reference each cell name as a range then look at .Offset (x,0) but tha
was a lot of additional coding and probably slower.

Your help would be apprecited,
Thanks

 

[Bernie Deitrick]

cparsons,

You can get the column number of the named range by using

Dim FNCol As Integer
FNCol = Range("FNAME").Column

First_Name(x) = .Offset(x, FNCol-1)

HTH,
Bernie
MS Excel MVP