Dealing with column headers

[Pawan]

Hi...

I am learning about writing macros. I always stuck up at one point.
I need to do various activies on columns based on their headings. e.g. I
have to replace all "NA" in the column whose heading is "TOWN". Currently I
write the macro using column number. But if the columns gets shifted, then
this macro will not work. How can I extract column number by identifying its
heading?

Regards,
Pawan

 

[Daniel Gash-Jones]

try this

for x = 1 to columns.count
if columns(x).cells(1).value2 = YourValue then exit for
next x

x is then the column number so you would reference as such columns(x)

To go one step further you could wrap this in a function

public function get_column(byval YourValue as string) as integer
for x = 1 to columns.count
if columns(x).cells(1).value2 = YourValue then
get_column = x
exit function
end if
next x
end function

HTH

 

[Bob Phillips]

colNo = Application.Match("TOWN",Rows(1),0))

and use colNo.

 

[Daniel Gash-Jones]

lol good point bob. no point reinventing the wheel!

 

[Pawan]

Thats perfect...

Thanks Bob..

colNo = Application.Match("TOWN",Rows(1),0))

and use colNo.


--
__________________________________
HTH

Bob