smartin said:
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