Iteratively Get Array Position Index and Update Array Value

[jleco]

Hi I am trying to create a program in VBA where I have an Array A and I am trying to Iteratevely find the minimum value in the array, get its position in the array and next update its value to an arbitrary large number (say 9999).

The code I have come up with so far is something like this

For j = 0 To Len(A)​

MinValue = Application.WorksheetFunction.Min(A)
ArrayPos = Application.WorksheetFunction.Match(MinValue, A, 0)
A(ArrayPos) = 9999

Next j​

However the Variable ArrayPos which is the index of the position in the array never seems to get updated after the first iteration.

Does any body have any ideas what can be the problem?

Thanks in advance

 

[Kenneth Hobson]

Welcome to the forum!

See if this helps.

'If array is 0 based, 1 is returned if aValue matches anArray(0).
Function PosInArray(aValue, anArray)
  Dim pos As Long
  On Error Resume Next
  pos = -1
  pos = WorksheetFunction.Match(CStr(aValue), anArray, 0)
  PosInArray = pos
End Function

 

[Rick Rothstein]

Hi I am trying to create a program in VBA where I have an Array A and I am trying to Iteratevely find the minimum value in the array, get its position in the array and next update its value to an arbitrary large number (say 9999).

The code I have come up with so far is something like this

For j = 0 To Len(A)​

MinValue = Application.WorksheetFunction.Min(A)
ArrayPos = Application.WorksheetFunction.Match(MinValue, A, 0)
A(ArrayPos - 1) = 9999

Next j​

However the Variable ArrayPos which is the index of the position in the array never seems to get updated after the first iteration.

Does any body have any ideas what can be the problem?

First off, did you mean UBound(A) in place of what I highlighted in Green above?

Since you are starting your loop at 0, can I assume your array is a normal one-dimensional VB array and not a two-dimensional variant array that was assigned from a range? If so, then I think you need to subtract one from the ArrayPos as shown in red above because I am pretty sure the Match function assumes the first element of the array is index 1 whereas the VB array numbers it starting element as 0 (assuming Option Index 1 is not in effect) which would mean you are changing the wrong element (hence the minimum value never changes).

 

[jleco]

Yes!

This was the problem!

Thank you very much!

 

[jleco]

Thank you, I didn't know the index properties of the match function. That's what I needed to get sorted