ExcelBanter - Size of arrayformula with VBA

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Size of arrayformula with VBA (https://www.excelbanter.com/excel-programming/340656-size-arrayformula-vba.html)

Size of arrayformula with VBA

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

Size of arrayformula with VBA

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

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

Size of arrayformula with VBA

Bob,

Thank you! That was exactly what I eas trying to do!

/Sune

"Bob Phillips" wrote:

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 & "])"