select a column from a range

[Ian Mangelsdorf]

i am trying to seach through a row of data and then select the column
under it using the code below. When im looping through the code the
variable col always returns "1", should it not return a number
relating to the column of the cell

Set header_rng = total.Range("b5:ao5")

For Each cell In header_rng
If cell.Value = "LAST YR" Then
col = cell.Columns.Count
Set change_range = Range(Cells(7, col), Cells(80, col))

change_range.Select

End If
Next cell

is there a better way to do this?

Cheers

Ian

 

[mudraker]

you are counting the number of coulumns that cell covers which is 1


use

col = cell.Column

 

[Frank Kabel]

Hi
try
Set header_rng = total.Range("b5:ao5")

For Each cell In header_rng
If cell.Value = "LAST YR" Then
col = cell.column
Set change_range = Range(Cells(7, col), Cells(80, col))
change_range.Select
exit for
End If
Next cell

 

[Ian Mangelsdorf]

Thanks all