Size of arrayformula with VBA
Hi Bob,
If I'm reading this correctly, it reads the size of the area in Sheet3 and
returns values from Sheet1.
It is almost what I'm looking for, except I would like to find the area
containing values in Sheet1 and the using the size of this area to insert the
formula in Sheet3.
Thanks for the efford, thoug!
/Sune
"Bob Phillips" wrote:
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
|