Changing cell content on n auto-filtered column

[Colin Hayes]

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.

 

[Claus Busch]

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

 

[Colin Hayes]

Hi Colin,

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


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

 

[Claus Busch]

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

 

[Colin Hayes]

Hi Colin,

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


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