Retrieve The Index Number Value From An Array

The code snippet below extracts the maximum value in a range of cells., this is accomplished through a one-dimensional array.  While I can now identify the maximum value, I also need to identify the index number in the array so I can link it back to the actual cell value.  Each attempt I've made to retrieve the Index value has resulted in a VBA error message.

axla

Public Sub findMaxVal()

Dim var(1 To 12)
Dim varMax As Double
Dim cnt As Long
Dim hold As Long
Dim holdr As String
Range("e2").Select

Do Until cnt = 12
cnt = cnt + 1
var(cnt) = ActiveCell
ActiveCell.Offset(0, 1).Select
Loop
hold = Application.WorksheetFunction.Max(var())
MsgBox hold
End Sub
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:

Sub test()

MsgBox Application.WorksheetFunction.Max(Range("e2").Resize(12))

End Sub

Thomas
Commented:

Sub test()
Dim cl As Range, rng As Range, maxValue As Double

Set rng = Range("e2").Resize(12)

maxValue = Application.WorksheetFunction.Max(rng)

For Each cl In rng.Cells
Next cl

MsgBox "Max is " & maxValue & Chr(10) & "Max Cells: " & Chr(10) & _
End Sub
Commented:
sorry, your range is going to the right, not down, so use resize(1,12) instead of resize(12)

Otherwise, the following formula will return your index in the array:
Application.WorksheetFunction.Match(hold, var(), 0)

Thomas

Author Commented:
Thomas,

Thanks for the information, and I apologize in advance for all of my questions.  In looking at your code snippet, I just want to make sure I understand the process.  You define "rng" and "cl" as Ranges, "maxValue" as a Double, and "strAdd" as a String.  You use the maxValue variable to capture the maximum value of the rng array.  You use the strAdd string variable to capture the cell address of the rng array index value when that value is equal to the value stored in the maxValue variable.   Do you need the cl array variable only since you are relooping through the rng array to find a match?  Is it possible to capture both at the same time or is this the best way to perform the task?  If you were attempting to do everything in one pass would I need to have declared a different array dimension?  When I tried this:  "Application.WorksheetFunction.Match(hold,var(), 0)" in my original code block, I received this error message:  "Run-Time error '1004':  Unable to get the Match property of the WorksheetFunction class."  The code snippet listed below is a modified version of your code that I got to work.  While the code currently displays the values in a message box, what I would ultimately like to do is to bold the text for each maximum value in the array.  Thanks again for all of your help.
Author Commented:
Thomas,

I forgot to provide the modified code snippet.  I was able to update the cell display like I wanted.  Thanks again.

Aaron

Sub findMax()
Dim cl As Range, rng As Range, maxValue As Double

Do Until IsEmpty(ActiveCell)
Set rng = ActiveCell.Resize(1, 12)
maxValue = Application.WorksheetFunction.Max(rng)

For Each cl In rng.Cells
Next cl

Selection.Font.Bold = 3
Selection.Interior.ColorIndex = 6
ActiveCell.Offset(1, 0).Select
Loop

End Sub
Commented:
The Match will only return the first value so the loop is a better way to get all the matching cells.

I'm not looping twice on rng. I'm using max() to get the data, then I'm looping to get the address.

To bold the text, you can just drop the strAdd part and do the attached code. You can also do it outside of VBA and use conditional formatting to bold the maximums, this would be in most cases a preferred solution.
``````Sub test()
Dim cl As Range, rng As Range, maxValue As Double

Set rng = Range("e2").Resize(12)

maxValue = Application.WorksheetFunction.Max(rng)

For Each cl In rng.Cells
If cl = maxValue Then cl.Font.Bold
Next cl
End Sub
``````
Commented:
I believe the code you uploaded will work only if you have one cell equal to the max. With two cells to the max, the Range(strAdd).Select will just take the last cell to match, plus it will become the active cell, . Your code can be updated as follows:

Thomas
``````Sub findMax()
Dim cl As Range, rng As Range, maxValue As Double

Do Until IsEmpty(ActiveCell)
Set rng = ActiveCell.Resize(1, 12)
maxValue = Application.WorksheetFunction.Max(rng)

For Each cl In rng.Cells
If cl = maxValue Then
cl.Font.Bold = 3
cl.Interior.ColorIndex = 6
Next cl

ActiveCell.Offset(1, 0).Select
Loop

End Sub
``````

Experts Exchange Solution brought to you by