Dynamic range in a formula array

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[4开发者_JAVA技巧9])"

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?

---

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)

---

Just write your function as if you are doing it in first cell. For exmaple:

Range("A1:C150")= "=C33*D21+Countif(G100:G500,"">0"")"

You will get the same result by writing the formula =C33*D21+Countif(G100:G500,">0") in range A1 and coping it to range A1:C150

---

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