determine which column has a value

  • Thread starter Thread starter keyser_Soze
  • Start date Start date
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.
 
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
 
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'.
 
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,
 
How can express a range that contains all the columns from
rtotals.column+1 to last used column?
 
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.
 
Back
Top