determine which column has a value

K

keyser_Soze

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.
 
J

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
 
K

keyser_Soze

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'.
 
G

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,
 
K

keyser_Soze

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