Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 561
  • Last Modified:

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
0
axla
Asked:
axla
  • 5
  • 3
1 Solution
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now