Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Changing cell content on n auto-filtered column
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. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing the cell format doesn't change existing cell content | Excel Discussion (Misc queries) | |||
Auto delete cell content | Excel Discussion (Misc queries) | |||
Changing cell colour depending on content?? | Excel Programming | |||
Changing row content with single cell click | Excel Discussion (Misc queries) | |||
changing font color of cell content | Excel Programming |