Reduce number of function calls

[mookyon]

Hi Everybody,



1) I am trying to reduce the number of functions calls in a loop (from 4 to one);

2) If possible, I also wish to find an alternative to “Application.Index” calls. Please advise.

Please look at the following --
(In short: we are transposing a vertical worksheet region into a four-consecutive-columns in a certain row of a VBA two-dimentional variant array)

' STORE worksheet's four results into vba array: varTests

varTests(t, 2) = Application.Index(wrkshtMAIN.Range("rgn?RunScore"), 1, 1)

varTests(t, 3) = Application.Index(wrkshtMAIN.Range("rgn?RunScore"), 2, 1)

varTests(t, 4) = Application.Index(wrkshtMAIN.Range("rgn?RunScore"), 3, 1)

varTests(t, 5) = Application.Index(wrkshtMAIN.Range("rgn?RunScore"), 4, 1)

I wish to replace these four calls with a more efficient code (maybe executing one call instead of four).

I’ve tried placing “Array(1,2,3,4)” at the row index of Application.Index but failed (probably did it wrong).

I am not experienced in VBA. I hope one of the members will be able to provide a hint.



1000 THX

 

[Peter_SSs]

(In short: we are transposing a vertical worksheet region into a four-consecutive-columns in a certain row of a VBA two-dimentional variant array)

I missed this earlier. Please do not write in such small font size unless there is a particular reason to do so.

If varTests is dimed as, say, (1 to 10, 1 to 5) then as far as I am aware you cannot read values into, say varTests(3,2), varTests(3,3), varTests(3,4), varTests(3,5) other than either individually as you originally showed or individually via a loop as already suggested.

 

[mookyon]

1000 THX Peter_SSs.
I will mark it complete (and will watch for font size)