determine which column has a value

[[email protected]]

I am using =MATCH("Totals",B1:AZ1,0) to tell me which column 'totals'
is in. However, the result is an integer and I would like to use this
column in a range("x:x+3") statement.

How can I calculate the above and be able to use the results in a
range() statement?

Ultimately, I want to be able to do this:
If Not Intersect(target, Range("B:J")) Is Nothing Then target.Offset(0,
11).Select
with a variable range of columns.

Thanks.

[JE McGimpsey]

Perhaps

Dim rTotal As Range
Set rTotal = Rows(1).Find( _
What:="Totals", _
LookIn:=xlValues, _
LookAt:=xlWhole, _
MatchCase:=False)
If Not rTotal Is Nothing Then _
If Target.Column >= 2 And Target.Column <= rTotal.Column Then _
Target.Offset(0, 11).Select

In article <[email protected]>,
[email protected] wrote:

> I am using =MATCH("Totals",B1:AZ1,0) to tell me which column 'totals'
> is in. However, the result is an integer and I would like to use this
> column in a range("x:x+3") statement.
>
> How can I calculate the above and be able to use the results in a
> range() statement?
>
> Ultimately, I want to be able to do this:
> If Not Intersect(target, Range("B:J")) Is Nothing Then target.Offset(0,
> 11).Select
> with a variable range of columns.
>
> Thanks.

[[email protected]]

Looks good except Target.Offset(0, 11).Select because the 11 is no
longer static. Instead 11 should be the distance between column b and
the targetcolumn, ie if a doubleclick is in the first column of the
range, the select should be to the column after 'totals'.

[George Nicholson]

You can probably use the integer:
Intersect(target, range(columns(2),columns(x)))

There may be a more elegant/concise way to express that, but if so, it
escapes me at the moment.

HTH,
--
George Nicholson

Remove 'Junk' from return address.


<[email protected]> wrote in message
news:[email protected]...
>I am using =MATCH("Totals",B1:AZ1,0) to tell me which column 'totals'
> is in. However, the result is an integer and I would like to use this
> column in a range("x:x+3") statement.
>
> How can I calculate the above and be able to use the results in a
> range() statement?
>
> Ultimately, I want to be able to do this:
> If Not Intersect(target, Range("B:J")) Is Nothing Then target.Offset(0,
> 11).Select
> with a variable range of columns.
>
> Thanks.
>

[[email protected]]

How can express a range that contains all the columns from
rtotals.column+1 to last used column?

[JE McGimpsey]

If I understand you correctly, try

Target.Offset(0, rTotal.Column - 1).Select


In article <[email protected]>,
[email protected] wrote:

> Looks good except Target.Offset(0, 11).Select because the 11 is no
> longer static. Instead 11 should be the distance between column b and
> the targetcolumn, ie if a doubleclick is in the first column of the
> range, the select should be to the column after 'totals'.

[JE McGimpsey]

One way:

With rTotals.Parent
Set rTest = Intersect(Range(rTotals(1, 2), .Cells( _
.Rows.Count, .Columns.Count)), .UsedRange).EntireColumn
End With

Note, this assumes that there's at least one column to the right of
rTotals in .UsedRange.



In article <[email protected]>,
[email protected] wrote:

> How can express a range that contains all the columns from
> rtotals.column+1 to last used column?