Match / Name-assigning To A Range
 

I have a question concerning the match-function combined with the namin
of a range.

let's suppose my data lies in a range of 3x3 cells. I don't know wher
this range is on my worksheet, but because there's a title on top of i
(e.g. "data"), I can find its row with

=MATCH("data";A1:A20;0)

If I want to give this range a title, I would normally do it using

ActiveWorkbook.Names.Add Name:="Daten1", RefersToR1C1:
_"=Sheet1!R6C1:R8C3"

(in case the data is in A6:C8)

Now the question: how can I combine those two expressions in that th
value returned by the match-function should be an argument for th
AddName-function? Can I use variables? Any other way?

Thanks already and happy easter,

Fabaliciou

--
Message posted from http://www.ExcelForum.com

Match / Name-assigning To A Range
 

Try the following macro:

Answer = WorksheetFunction.Match("data", Range("A1:A20"),
0)
Range(Cells(Answer, 1), Cells(Answer + 2, 3)).Name
= "Daten1"

Regards
Klaus

-----Original Message-----
I have a question concerning the match-function combined
with the naming
of a range.

let's suppose my data lies in a range of 3x3 cells. I
don't know where
this range is on my worksheet, but because there's a
title on top of it
(e.g. "data"), I can find its row with

=MATCH("data";A1:A20;0)

If I want to give this range a title, I would normally do
it using

ActiveWorkbook.Names.Add Name:="Daten1", RefersToR1C1:=
_"=Sheet1!R6C1:R8C3"

(in case the data is in A6:C8)

Now the question: how can I combine those two expressions
in that the
value returned by the match-function should be an
argument for the
AddName-function? Can I use variables? Any other way?

Thanks already and happy easter,

Fabalicious


---
Message posted from http://www.ExcelForum.com/

.