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

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

and use colNo.


--
__________________________________
HTH

Bob

"Pawan" <(E-Mail Removed)> wrote in message
news:CB79315B-1E8E-4CB1-96D6-(E-Mail Removed)...
> 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]

lol good point bob. no point reinventing the wheel!

Thats perfect...

Thanks Bob.. :-)

"Bob Phillips" wrote:

> colNo = Application.Match("TOWN",Rows(1),0))
>
> and use colNo.
>
>
> --
> __________________________________
> HTH
>
> Bob
>
> "Pawan" <(E-Mail Removed)> wrote in message
> news:CB79315B-1E8E-4CB1-96D6-(E-Mail Removed)...
> > 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
>
>
>