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...2&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...
"Rick Rothstein (MVP - VB)" wrote:
> If you have to ask if using On Error Resume Next is okay to do, I'd have to
> say no, it is not okay... you should only use that statement when you
> understand the ramifications of doing so; that is, you know the types of
> errors that can be generated and you know that your code can survive those
> errors if they are ignored. Now, as to your other question... there was too
> many postings to go through to in order to figure out where the code you
> were talking about is (it is usually best to post any code directly in your
> message so the volunteers here don't have to go searching for it).
>
> I'm not sure what you want to do if the user clicks Cancel (end the
> subroutine, substitute in a default value for the rest of the code to use,
> or something else), so let me give you a general structure that you can use
> after an InputBox statement in order to determine whether the user pressed
> Cancel or not.... then you can replace my MsgBox statements with the code
> you want to execute for the various possibilities. Copy/Paste the following
> macro code into a code window and run it... try clicking Cancel, Enter with
> no text and then Enter with some text in order to see how you can trap each
> of these possible actions.
>
> Sub Test()
> Dim strInput As String
> strInput = InputBox("Some prompt for input")
> If Len(strInput) = 0 Then
> If StrPtr(strInput) = 0 Then
> MsgBox "User clicked Cancel Button"
> Else
> MsgBox "No text entry, user clicked Enter"
> End If
> Else
> MsgBox "The user inputted some text"
> End If
> ' The subroutine code continues here
> End Sub
>
> Rick
>
>
> "broro183" <(E-Mail Removed)> wrote in message
> news:31FAA6B9-BE1C-4EA4-BF86-(E-Mail Removed)...
> > hi all,
> >
> > StephenR & myself have been helping a user modify a macro which will
> > coordinate price changes on inventory based on user inputs from Input
> > boxes
> > at http://Excelforum.com. If the Input box is cancelled a mismatch error
> > occurs due (I think) to the previous variable declaration as long.
> > 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?
> >
> > For more background please refer to the below thread & can you please
> > reply
> > to the below thread for the original op?
> > http://excelforum.com/showthread.php?t=621111
> >
> > Also, feel free to make any suggestions for improving the current code...
> >
> > I apologise if this is not the best way for asking for help. If it's not,
> > can someone please let me know how I should ask?
> >
> > Thanks
> > Rob
> >
> > __________________
> > Rob Brockett
> > NZ
> > Always learning & the best way to learn is to experience...
> >
> >
>
>
Similar Threads
