Thread
:
Size of arrayformula with VBA
View Single Post
#
4
Posted to microsoft.public.excel.programming
Bob Phillips[_6_]
external usenet poster
Posts: 11,272
Size of arrayformula with VBA
Do you mean
Dim iLastRow As Long
Dim iLastCol As Long
With Worksheets("Ark")
iLastRow = .Cells.Find(What:="*", _
After:=.Range("A1"), _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
iLastCol = .Cells.Find(What:="*", _
After:=Range("A1"), _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
End With
Range("A2", Worksheets("Sheet1").Cells(iLastRow, iLastCol)).FormulaArray
= _
"=IF(Ark1!RC:R[" & iLastRow & "]C[" & iLastCol & _
"]="""","""",Ark1!RC:R[" & iLastRow & "]C[" & iLastCol & "])"
--
HTH
Bob Phillips
"Sune Fibaek" <Sune
wrote in message
...
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
Reply With Quote
Bob Phillips[_6_]
View Public Profile
Find all posts by Bob Phillips[_6_]