I tried this

Dim iLastRow As Long
Dim iLastCol As Long
iLastRow = Cells.Find(What:="*", _
After:=Range("A1"), _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row

iLastCol = Cells.Find(What:="*", _
After:=Range("A1"), _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column

Range("A2", Cells(iLastRow, iLastCol)).FormulaArray = _
"=IF(Ark1!RC:R[" & iLastRow & "]C[" & iLastCol & _
"]="""","""",Ark1!RC:R[" & iLastRow & "]C[" & iLastCol & "])"

trouble is that it gives a circular reference as the formula refers to the
cells it is in. What should happen?

--
HTH

Bob Phillips

"Sune Fibaek" wrote in message
...
Hi,

In Sheet1 i have an area with values. Ie A3:AQ12500 contains values
determined by formulas. I want to add an arrayformula in Sheet3 that
mirrors this data.

Currently i'm using:
Range("A2:AQ15000").Select
Selection.FormulaArray =
=IF(Ark1!RC:R[15000]C[42]="""","""",Ark1!RC:R[15000]C[42])"

The problem is, that this is not very flexible, as I have to know the
size of the area in Sheet1. I don't want to simply copy the values as
they may change.

How do I find the area from A3:IV65536 that acctually contains values
and add that to my arrayformula?

My first thought was Range("A3").CurrentRegion from Sheet1, but I can't
quite figure it out.

Thanks!

/Sune