Colin Hayes wrote:
In article , smartin
writes
Colin Hayes wrote:

Hi

I need a small macro to find the lowest number in a given column , and
then replace it with a number input through a popup.


Can anyone help?


Grateful for any assistance.


Try this:

Sub ReplaceIt()
Dim TheMin As Variant
Dim TheOffset As Long
Dim TheCell As String
Dim TheNewValue As Variant

TheMin = Application.WorksheetFunction. _
Min(Range("A:A"))
TheOffset = Application.WorksheetFunction. _
Match(TheMin, Range("A1:A9999"), 0)
TheCell = Range("A1").Offset(TheOffset - 1).Address

TheNewValue = InputBox("Minimum value found was " & _
TheMin & ". Enter value to replace.")
Range(TheCell).Value = TheNewValue
End Sub

Hi

OK thanks very much for helping.

It works fine , but only changes one value , rather than all the
matching ones.

For example , it found the lowest to be 1.95 and I asked it to change
this to 1.25. It did it , but only to the first occurrence of a cell
with 1.95 and left all the others intact. Could it be tweaked to change
all cells with the lowest value?

Also , because the column I need it to work on could vary would it be
possible to enter the column to be selected via a popup and for it to
work on the whole column down to the last row wherever that is?

Grateful again for you advice.



Best Wishes

Hi Colin, sorry for the late reply.
Without creating a user form, the approach I would take would be:

- Sub1 with input box to query user for the column, call and pass result
to Sub2.

- Sub2 with input box to query user for min value, then leverage
Application.WorksheetFunction.Replace and the column reference obtained
above to do the heavy lifting.

Sorry I don't feel like coding just now, post back if you get stuck!