Size of arrayformula with VBA

[Sune Fibaek]

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

 

[Bob Phillips]

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?

 

[Guest]

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

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

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

 

[Bob Phillips]

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

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

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

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

 

[Guest]

Bob,

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

/Sune