Match / Name-assigning To A Range

F

fabalicious

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
 
K

Klaus

Try the following macro:

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

Regards
Klaus
 
Top