Encontrar celda donde esta el valor máximo y mínimo en un Rango de Datos EXCEL VBA

Esta es la solución a tu pregunta:

Esta macro te mostrará la dirección en la que se encuentra el valor máximo del rango D2:D40 de la hoja1

Sub buscamaximo()
Set rango = Sheets("hoja1").Range("d2:d40")
Set busca = rango.Find(Application.WorksheetFunction.Max(rango), LookIn:=xlValues, lookat:=xlWhole)
If Not busca Is Nothing Then
ubica = busca.Address
End If
MsgBox "el valor máximo se encuentra en el rango " & ubica
End Sub

Recuerda finalizar y puntuar

Amigo muchas gracias por la respuesta, pero creo que algo pasa porque no dio resultado. quizá debí decir también que en el rango de datos en el cual quiero buscar el valore máximo numéricos, ese rango esta compuesto por formulas, no sé si quizá por eso no de resultado el código que has brindado. Muchas gracias nuevamente. Un fuerte abrazo

La macro funciona perfectamente incluso con fórmulas. Te vuelvo a adjuntar la macro para que muestre también el valor mínimo:

Recuerda que el análisis lo hacemos en el rango D2:D40. Conforme a tu pregunta.

Sub buscamaximo()
Set rango = Sheets("hoja1" ).Range("d2:d40")
Set buscamax = rango.Find(Application.WorksheetFunction.Max(rango), LookIn:=xlValues, lookat:=xlWhole)
Set buscamin = rango.Find(Application.WorksheetFunction.Min(rango), LookIn:=xlValues, lookat:=xlWhole)
If Not buscamax Is Nothing Then
ubica1 = buscamax.Address
End If
If Not buscamin Is Nothing Then
ubica2 = buscamin.Address
End If
MsgBox "el valor máximo se encuentra en el rango "& ubica1 & Chr(13) & "y el valor mínimo está en el rango "& ubica2
End Sub

Amigo muchas gracias por seguir ayudándome, te comento que acabo de probar el código en un rango en el cual había números normales y si funciona, pero cuando lo pruebo en el rango en el cual quiero buscar no resulta.

Me di cuenta también que el rango donde quiero buscar aparte de ser una formulas, los valores de sus celdas que se obtienen en ese rango son del tipo condicional, específicamente así:

=SI(C5=0,0,D5/C5)

Creo que eso ya no sería una formula sino también un condicional y seguro allí es la causa del error, disculpa por tanta molestia, te agradezco infinitamente amigo

Amigo otra consulta, como podría hacer también si es que quiero buscar el numero menor pero que sea mayor de cero.

He tomado una captura de la hoja de calculo, y de la situación especifica del problema que estoy planteando, el rango en donde deseo busca es el que esta sombreado de color rojo.

Infinitas gracias nuevamente

Seguramente sea por culpa de los decimales de algunas celdas. Te explico: la fórmula condicional que has comentado debería estar así:

=SI(C5=0,0,REDONDEAR(D5/C5,2))

De la manera que lo tenías el resultado es un número periódico puro y no lo encuentra.

Prueba ahora con esta macro que he reformado y recuerda arreglar las fórmulas como te he indicado para que el resultado esté acotado.

Sub buscamaximo()
Set rango = activesheet.Range("d2:d40")
Set buscamax = rango.Find(Application.WorksheetFunction.Max(rango), LookIn:=xlValues, lookat:=xlWhole)
Set buscamin = rango.Find(Application.WorksheetFunction.Min(rango), LookIn:=xlValues, lookat:=xlWhole)
If Not buscamax Is Nothing Then
ubica1 = buscamax.Address
End If
If Not buscamin Is Nothing Then
ubica2 = buscamin.Address
End If
MsgBox "el valor máximo se encuentra en el rango "& ubica1 & Chr(13) & "y el valor mínimo está en el rango "& ubica2
End Sub

Amigo tenias toda la razón, hice los cambios que me comentaste y lo encontró, ahora para finalizar, quería consultarte algo último.

Cuando quiero encontrar el mínimo, quisiera que encuentre el mínimo pero que sea mayor que cero.

Infinitamente agradecido por tu respuesta amigo.

Aquí tienes la macro con tus últimas peticiones:

Sub buscamaximo()
Set rango = ActiveSheet.Range("d2:d40")
Set buscamax = rango.Find(Application.WorksheetFunction.Max(rango), LookIn:=xlValues, lookat:=xlWhole)
Set buscamin = rango.Find(Application.WorksheetFunction.Min(rango), LookIn:=xlValues, lookat:=xlWhole)
If Not buscamax Is Nothing Then
ubica1 = buscamax.Address
End If
If Not buscamin Is Nothing Then
ubica2 = buscamin.Address
Do
If buscamin = 0 Then
Set buscamin = rango.FindNext(buscamin)
ubica3 = buscamin.Address
Exit Do
End If
Loop While Not buscamin Is Nothing And buscamin.Address <> ubica2
End If
MsgBox "el valor máximo se encuentra en el rango "& ubica1 & Chr(13) & "y el valor mínimo está en el rango "& ubica2
End Sub

Amigo me vas a querer matar :(, disculpas por preguntarte tanto, he probado el código final y resulta con el valor máximo pero con el valor mínimo, me bota en blanco. no se en donde puede estar el problema he tratado de entender este ultimo código, pero entiendo cuando se guarda en la variable ubica3, si al final se muestra la variable ubica2.

Así como la línea de código:

Loop While Not buscamin Is Nothing And buscamin.Address <> ubica2

Te pido disculpas si te estoy causando incomodidad y a la vez muchas gracias por tu ayuda.