Find value, put it in an array and find the smallest value in the array VBA

Question

I would like to find a given value in a range, and if I find the value I would like to put it in an array. After the loop I would like to give the smallest value in the array to a variable.

How is is possible?

    For k = 2 To LastRow_Roles
        If roles_sheet.Cells(k, 2).Value = "Admin" Then
            selected_number = roles_sheet.Cells(k, 1).Value
            ReDim aArray(0 To UBound(aArray) + 1) As Variant
            aArray(UBound(aArray)) = selected_number        
         end if
    next k

minimum_value = Application.min(aArray)
low_sheet.Cells(1, 1) = minimum_value
Erase aArray
ReDim aArray(0 To 1) As Variant

luke_t · Accepted Answer · 2016-03-03 15:30:08Z

1

You will need to use ReDim Preserve so that you're preserving the existing data that has been appended to the array.

You can then use the Application.WorksheetFunction.Min function to extract the Min number found within the array.

For k = 2 To LastRow_Roles
    If roles_sheet.Cells(k, 2).Value = "Admin" Then
        ReDim Preserve aArray(0 To UBound(aArray) + 1)
        aArray(UBound(aArray)) = roles_sheet.Cells(k, 1).Value
    End If
Next k

minimum_value = Application.WorksheetFunction.Min(aArray)
low_sheet.Cells(1, 1) = minimum_value
Erase aArray
ReDim aArray(0 To 1) As Variant

answered Mar 3, 2016 at 15:30

luke_t

2,9874 gold badges26 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Erika Over a year ago

thank you. Just one question. When I am using Application.WorksheetFunction.max(aArray) it gives back rounded value. I see only 66 intead of 66.4684. Can you please help me with this? I tried Long and double instead of Variant but it is not working.

luke_t Over a year ago

what have you declared the variable you're assigning the maximum value to? If it's got decimal places it should be a double. Dim max_value as Double. Otherwise, debug and see if the array is populating with decimal values.

Erika Over a year ago

you are right. The maximum_value was Long. With Double is working like a charm.

Collectives™ on Stack Overflow

Find value, put it in an array and find the smallest value in the array VBA

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related