Create macro that calculates the same formula depending on how much data

Hi,

attached is a simple macro im playing around with. What I want to do is that depending on what data I put in the tab "admindata", the formula should follow how many rows that are filled in "Artno" in tab "RRP". Now the formula is based on a specific range. So depending how much data I put in "Admindata", the formula should follow how many rows that are filled in tab "RRP".

Hi !

Just apply a name for the range and use this name within formula …

okay, but I want the range to depend on how much data it is in column A. Will this work?

What I mean is that

PHP Code: 

 Range("B2").Select
    Selection.FormulaArray = _
        "=INDEX(admindata!R2C5[B]:R8827[/B]C5,MATCH(RRP!R1C&RRP!RC1,admindata!R2C1:R8827C1&admindata!R2C2:R8827C2,0),1)"
    Selection.AutoFill Destination:=Range("B2:R2"), Type:=xlFillDefault
    Range("B2:R2").Select
    Selection.AutoFill Destination:=Range("B2:[B]R519[/B]"), Type:=xlFillDefault
    Range("B2:R519").Select 


The formula now goes to the index in "admindata" up to row 8827 -> which I want to go to the last cell in that range. And the "autofill" only goes to row 519, which I want to as far as how much data I have in column A in tab "RRP". How can I make this happen?

can anyone help me?