2

How do I insert a sort function with VBA, so the whole range gets sorted.

This is the function I want to insert:

=SORT(A2:B" & LastRowB & ",2,-1,FALSE)

What i get is

=@SORT(A2:B3662;2;-1;FALSE)

This one is without the needed spill function

Mark
  • 21
  • 1

1 Answers1

3

For reasons of backwards compatibility, the default behaviour for things like Range("A1").Formula is the non-array, single result function. This is by design and prevents formulas that have been created before Dynamic Arrays existed, so existing VBA solutions won't all of a sudden calculate those functions that are now array aware as arrays.

If you want to get the array version instead of the single version of the formula, you can use

Range("A1").Formula2 = Sort(.....)

teylyn
  • 34,374
  • 4
  • 53
  • 73