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?
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
