Using input message box to insert variable into a filtering routine to change filter criteria - Help please?

[dkjonesau]

Hi all,

After a period of frustration and taking a break from a project I've bitten the bullet & revisited it.

I am trying to avoid writing a whole raft of routines to do the same thing. Any help appreciated.

What I need to do, is take a range of cells, copy them, paste them into another sheet. But I need to do it based on which group a referee wants on a score sheet.

Eg.

I have a sheet that looks like this...

Down the left column it goes all the way to 15 squads.

I've worked out that I can use a filter to select the squad I want based on the numbers in the left column.

I've played with this routine to do it.

Sub AutoFilter_in_Excel_Above_Below_Num()
Dim LR As Long

Range("B6", "L96").AutoFilter Field:=1, Criteria1:=">2", Operator:=xlAnd, Criteria2:="<3"

LR = Range("C" & Rows.Count).End(xlUp).Row
Range("C6:E" & LR).SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("Score").Select
Range("AG6").Select
'--->>> Fix Selection.Paste - haven't finished this bit yet.
End Sub

At present I've not sorted out the paste side of it. My challenge was to work out the best way of getting just one squad from the 15.

What I need to do now is to set that routine up so that I can use an input box to get the squad number that someone wants to generate a score sheet for, and get that number from the user inot the filter criteria above.

Where the filter looks for >2 & <3 above, I need to generate the value in place of 2 from a question in an input box, call it X, and then filter between X and X+1.

I've never used an input box to get a variable, and then used it in a routine before.

Any pointers or suggestions welcome thanks (says he with fingers crossed...)


Thanks

Dave

 

[My Aswer Is This]

Here is an Example of using Input Box

Sub Input_Box()
Dim x As Long

'Input in this example must be number
x = InputBox("Enter your value")
MsgBox x
End Sub

 

[dmt32]

Hi,


Application.InputBox Method may be what you need as can specify required data type.

As an example maybe:

Sub ApplicationInputBox_Method()
    Dim x As Variant
   
'Input numbers only
    x = Application.InputBox(prompt:="Enter Number", Title:="Enter Number", Type:=1)
'cancel pressed
    If VarType(x) = vbBoolean Then Exit Sub
   
    Range("B6", "L96").AutoFilter Field:=1, Criteria1:=">" & x, Operator:=xlAnd, Criteria2:="<" & x + 1
End Sub

you can read more about Application InputBox Method in the VBA helpfile.

Dave

 

[dkjonesau]

Thanks for that, I'm working that much out as I research while waiting to see what comes up here.

I presume MsgBox x tells VBA to return the result of the input to variable x.

I've called mine number, and I want numberp1 to equal number + 1.

I've played and gotten to this point:
Sub Select_Squad()
On Error GoTo NotValidInput
Dim number As Integer
Dim numberp1 As Integer
number = InputBox("Enter squad number for required scoresheet :")
MsgBox number
numberp1 = number + 1 ' Not sure if I've got this right.
Exit Sub
NotValidInput:
MsgBox ("You entered an invalid value!")
End Sub

If I've used a Call to this routine from another routine, will the variables pass back to the calling routine? Or do I have to build this into the routine that will use those variables?

Thanks,

Dave

 

[My Aswer Is This]

I used Msgbox just as a sample to show you what your inputbox value returned.
It's not needed really in your script.


If I've used a Call to this routine from another routine, will the variables pass back to the calling routine?

I believe so. Just try it and see.