Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 & "])" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CountA and ArrayFormula | Excel Worksheet Functions | |||
custom size (page size) missing in excel 2003 | Setting up and Configuration of Excel | |||
Cell size? Or size limit for Text data type? | Excel Discussion (Misc queries) | |||
Can we write VBA code to set all column/row's size back to default size? | Excel Programming | |||
Can we write VBA code to set all column/row's size back to default size? | Excel Programming |