Thanks Rick,
Sorry about the vague link I provided, to be more precise the latest
suggested code can be found at
http://excelforum.com/showpost.php?p=1847072&postcount=33
and this is the last post on
http://excelforum.com/showthread.php?t=621111&page=3. For ease of helpers I
have included the code below:
Option Explicit
Private AmountToChangeSmallBy As Long 'Inputbox variable
Private AmountToChangeMediumBy As Long 'Inputbox variable
Private AmountToChangeLargeBy As Long 'Inputbox variable
Private SingleOpt_LocationsToExclude As String 'Inputbox variable
Private MultipleOpt_LocationsToExclude As String 'Inputbox variable
Private sma As Range, med As Range, lar As Range, TempCell As Range
Sub newModifiedPriceChanger()
Application.ScreenUpdating = False
Dim CurrentCell As Range
Dim ws As Worksheet
Dim LastDataRow As Long
Set CurrentCell = ActiveCell
Call FlexibilityViaInput
For Each ws In ThisWorkbook.Worksheets
With ws
' .Select
'check that no rows are hidden
On Error Resume Next
.ShowAllData
On Error GoTo 0
LastDataRow = .Cells(Rows.Count, "A").End(xlUp).Row
Set sma = .Range("F2:F" & LastDataRow)
Set med = .Range("G2:G" & LastDataRow)
Set lar = .Range("H2:H" & LastDataRow)
Set TempCell = .Range("A" & LastDataRow + 1)
'to change all constant numeric values in the visible rows of the
sma & med ranges
If StrPtr(AmountToChangeSmallBy) <> 0 And AmountToChangeSmallBy <> 0
Then
Call GiveTempCellAValueAndChangeRange(AmountToChangeSmallBy, sma)
Else
MsgBox "No value was input therefore no changes will be made to
the sma range", vbOKOnly
End If
If StrPtr(AmountToChangeMediumBy) <> 0 And AmountToChangeMediumBy <>
0 Then
Call GiveTempCellAValueAndChangeRange(AmountToChangeMediumBy, med)
Else
MsgBox "No value was input therefore no changes will be made to
the med range", vbOKOnly
End If
'Creation of a helper column to filter based on Col I values
With .Range("L1:L" & LastDataRow)
.FormulaR1C1 = "=IF(OR(SUBSTITUTE(RC[-3],"" "","""")=" &
Chr(34) & _
SingleOpt_LocationsToExclude & Chr(34) & ",RC[-3]=" &
Chr(34) & _
MultipleOpt_LocationsToExclude & Chr(34) &
"),""hide"",""show"")"
'the below line has no error handling but may (?) need some
if there are already filters on the sheet...
.AutoFilter Field:=1, Criteria1:="show"
End With
'to adjust the lar range to only the constant numeric values in
visible cells
If StrPtr(AmountToChangeLargeBy) <> 0 And AmountToChangeLargeBy <> 0
Then
Call GiveTempCellAValueAndChangeRange(AmountToChangeLargeBy, lar)
Else
MsgBox "No value was input therefore no changes will be made to
the lar range", vbOKOnly
End If
'to remove the helper column & the temp cell
.Range("L:L").Delete
TempCell.ClearContents
End With
Next ws
'to leave the activecell highlighted at end of macro
CurrentCell.Select
Set CurrentCell = Nothing
Set ws = Nothing
Set sma = Nothing
Set med = Nothing
Set lar = Nothing
Set TempCell = Nothing
Application.ScreenUpdating = True
End Sub
Private Sub FlexibilityViaInput()
'error code added to allow for the input boxes being cancelled
On Error Resume Next
'use of Input boxes to allow flexibility through user input
AmountToChangeSmallBy = InputBox("please insert the amount to change the
""Small"" values by" _
& Chr(10) & "(eg to decrease by 2 enter ""-2"" or to increase enter
""2"")", _
"AMOUNT TO CHANGE BY:")
AmountToChangeMediumBy = InputBox("please insert the amount to change the
""Medium"" values by" _
& Chr(10) & "(eg to decrease by 2 enter ""-2"" or to increase enter
""2"")", _
"AMOUNT TO CHANGE BY:")
AmountToChangeLargeBy = InputBox("please insert the amount to change the
""Large"" values by" _
& Chr(10) & "(eg to decrease by 2 enter ""-2"" or to increase enter
""2"")", _
"AMOUNT TO CHANGE BY:")
SingleOpt_LocationsToExclude = InputBox("please type the SingleOpt_Locations
to exclude from the ""Large"" values being changed" _
& Chr(10) & "(eg ""JB, OT"")", _
"SingleOpt_Locations TO EXCLUDE")
MultipleOpt_LocationsToExclude = InputBox("please type the
MultipleOpt_Locations to exclude from the ""Large"" values being changed" _
& Chr(10) & "(eg ""JB, OT"")", _
"MultipleOpt_Locations TO EXCLUDE")
On Error GoTo 0
End Sub
Private Sub GiveTempCellAValueAndChangeRange(ChangeAmount As Long,
RangeToChange As Range)
'to create a temp cell value for paste special changes
With TempCell
'.Select
.Value = ChangeAmount
.Copy
End With
With RangeToChange.SpecialCells(xlCellTypeConstants,
1).SpecialCells(xlCellTypeVisible)
' .Select
.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, _
SkipBlanks:=False, Transpose:=False
End With
End Sub
If the user cancels the input this should be reacted to by the respective
code line eg "If StrPtr(AmountToChangeSmallBy) <> 0 And AmountToChangeSmallBy
<> 0".
Is this the best way of structuring this code when there the 3 separate
ranges that effectively have the same action occur ie user input/cancel and
then range values modified or not dependent on the input?
I thought it best to separate the Input section into a function but would
like to hear your thoughts - looking at your suggested technique would it be
best to deal with each range & the actions performed on them individually...
Hopefully, the inclusion of the code now clarifies my initial question about
the initial declaration of the variables...
"My reading suggests that it is best to declare variables explicitly rather
than use variants so to apply this I have used "on error resume" to make the
code work. Is this the best approach?"
(I'm off to sleep now, but will look again tomorrow.)
Thanks in advance
Rob
__________________
Rob Brockett
NZ
Always learning & the best way to learn is to experience...