Hi, I'm trying to implement to use an array formula to take a value and find the smallest difference from a list of known values. The array formula works fine when I manually input it:
' =MIN(ABS($A$5:$A$502-E5) ' + CTRL-SHIFT-ENTER
and when I record this using the macro recorder I get the following:
Selection.FormulaArray = "=MIN(ABS(R5C1:R502C1-RC[-13]))"
This line of code works just fine (in terms of the value it puts in the selected cell), but after the line executes, the VBA subroutine quits. I've tried several different variations in the syntax (ActiveCell vs. Selection, R[0]C[-13] vs RC[-13], etc.) but the subroutine always quits as soon as the line is executed. Is it possible that some error is occurring that I am not seeing? Or might this be a different problem? My grasp of VBA is not great, but passable.
' =MIN(ABS($A$5:$A$502-E5) ' + CTRL-SHIFT-ENTER
and when I record this using the macro recorder I get the following:
Selection.FormulaArray = "=MIN(ABS(R5C1:R502C1-RC[-13]))"
This line of code works just fine (in terms of the value it puts in the selected cell), but after the line executes, the VBA subroutine quits. I've tried several different variations in the syntax (ActiveCell vs. Selection, R[0]C[-13] vs RC[-13], etc.) but the subroutine always quits as soon as the line is executed. Is it possible that some error is occurring that I am not seeing? Or might this be a different problem? My grasp of VBA is not great, but passable.
