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...

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" wrote in message
...
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...

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(AmountToChangeSma llBy, 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(AmountToChangeMed iumBy, 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(AmountToChangeLar geBy, 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" wrote in message
...
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...