Hi All

I use this code to identify the lowest number in a column , and then
offer to change it :

Dim TheColumn As Variant
Dim TheRange As String
Dim TheMin As Variant
Dim TheNewValue As Variant
Dim OriginalRange As String
Dim OriginalCell As String

On Error GoTo Quitter

Application.ScreenUpdating = True
OriginalRange = Selection.Address
OriginalCell = ActiveCell.Address


'UnRem / Rem next line to have a popup ask for the column

TheColumn = InputBox(vbCr & "Change lowest price in which column?",
"Price Variation")


'OR UnRem / Rem next two lines to have macro work on an
already-selected or highlighted column

' TheColumn = Left(OriginalCell, _
' InStr(2, OriginalCell, "$") - 1)


If TheColumn < "" Then
TheRange = TheColumn & ":" & TheColumn
TheMin = Application.WorksheetFunction. _
Min(Range(TheRange))
TheNewValue = InputBox(vbCr & "Minimum value found was " & _
TheMin & " ...." & vbCr & vbCr & "Enter value to replace.",
"Price Variation", TheMin)
If TheNewValue < "" Then
Range(TheRange).Select
Selection.Replace What:=TheMin, _
Replacement:=TheNewValue, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
Else
MsgBox "No input, operation cancelled."
End If

Range(OriginalRange).Select
Range(OriginalCell).Activate
Else
MsgBox "No input, quitting."
End If
Quitter:
Application.ScreenUpdating = True

Selection.NumberFormat = "0.00"

End Sub


This works fine on an unfiltered column.

Unfortunately when I try to apply it to column where I have used
auto-filter it no longer works properly.

Can someone suggest an amendment to the above code so that it only works
on the auto-filtered cells?

Grateful for any help.

Hi Colin,

Am Thu, 12 Apr 2012 21:19:29 +0100 schrieb Colin Hayes:

TheMin = Application.WorksheetFunction. _
Min(Range(TheRange))

try:
TheMin=Application.WorksheetFunction. _
Subtotal(5,Range(TheRange))


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

In article , Claus Busch
writes
Hi Colin,

Am Thu, 12 Apr 2012 21:19:29 +0100 schrieb Colin Hayes:

TheMin = Application.WorksheetFunction. _
Min(Range(TheRange))

try:
TheMin=Application.WorksheetFunction. _
Subtotal(5,Range(TheRange))


Regards
Claus Busch

Hi Claus

Thanks for that - it works perfectly on a filtered column.

Will this work equally well on an unfiltered column?

If so I can change the macro to your suggestion permanently as it would
then work in either circumstance.


Best Wishes

Hi Colin,

Am Thu, 12 Apr 2012 22:01:20 +0100 schrieb Colin Hayes:

Will this work equally well on an unfiltered column?

yes, it works also in unfiltered column


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

In article , Claus Busch
writes
Hi Colin,

Am Thu, 12 Apr 2012 22:01:20 +0100 schrieb Colin Hayes:

Will this work equally well on an unfiltered column?

yes, it works also in unfiltered column


Regards
Claus Busch


HI

OK that's fine. Thanks for getting back to confirm.

Grateful for your time and expertise.

Best Wishes