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
axlaAsked:
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.

nutschCommented:
How about just

Sub test()

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

End Sub

Thomas
0
nutschCommented:
or with the addresses:

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

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

maxValue = Application.WorksheetFunction.Max(rng)

For Each cl In rng.Cells
If cl = maxValue Then strAdd = strAdd & cl.Address & Chr(10)
Next cl

MsgBox "Max is " & maxValue & Chr(10) & "Max Cells: " & Chr(10) & _
            Left(strAdd, Len(strAdd) - 1)
End Sub
0
nutschCommented:
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

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

axlaAuthor 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.
0
axlaAuthor 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
Dim strAdd As String

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 strAdd = cl.Address
    Next cl

     strAdd = Replace(strAdd, "$", "")
     Range(strAdd).Select
     Selection.Font.Bold = 3
     Selection.Interior.ColorIndex = 6
     ActiveCell.Offset(1, 0).Select
Loop

End Sub
0
nutschCommented:
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

Open in new window

0
nutschCommented:
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
Dim strAdd As String
 
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

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
axlaAuthor Commented:
Thomas,

Thanks again.  I need to learn more on how to use arrays, your code was very straigt forward and very helpful and will be a good place for me to start.

Aaron
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.