dynamic range in a formula array

Question

Hi - VBA newbie over here. I'm trying to use an array formula through excel vba but I can't seem to specify a dynamic range for the formula. I have:

Range("xyz").FormulaArray = "=somefunction(Data!RC:R[8]C[49])"

But next time it could be Range("xyz").FormulaArray = "=somefunction(Data!RC:R[15]C[32])"

This doesn't seem to work. Is there any solution to this?

Thank you.

Answer 1

If by dynamic range you mean a range whose size is determined by VBA variables (for instance jRow and kCol) then

Range("xyz").FormulArrayR1C1="=somefunction(Data!RC:R[" & cstr(jRow) & "]C[" & cstr(kCol) & "])"

If you are asking how to determine how many rows and columns in an area are occupied look at

Range.CurrentRegion
Range.CurrentArray
Range.End(xlUp).Row (also xlDown, xlRight, xlLeft)

Answer 2

Hi, Not sure exactly what you're looking for, but maybe this will help. The code below uses the "CountA" function to check how many cells in the specified range have data in them (using A1:A10, but could be any range), and then copies that number of cells from worksheet1 to worksheet2. This will only work if the data is continuous (no blank cells in between the data).

On worksheet1, put some numbers in cells A1:A5, for instance...

Sub DynamicRange() Dim CountA_Range As Range, intCountA_Result As Integer, CopyRange As Range

Set CountA_Range = Worksheets(1).Range("A1:A10") intCountA_Result = WorksheetFunction.CountA(CountA_Range)

Set CopyRange = ThisWorkbook.Worksheets(1).Rows("1:" & intCountA_Result)

CopyRange.Copy
Worksheets(2).Rows("1").PasteSpecial (xlPasteValues)

End Sub