Macro Input Box not working

[eulerddx4]

Hello, I have a spreadsheet that I am trying to finish this morning and for some reason I can't get the Input box to work. My macro scans through code and filters data based on whether it is greater than a number, less than a number or the change between cells to the left of the selected cell is greater than a number.

I have added a couple lines of code to prompt the user to select the active cell but for some reason I am getting error code 424 Object required after I select the active cell.

Your help would be greatly appreciated!

Sub mypicker()
    Dim rng As Variant
    rng = Application.InputBox("Select the first velocity data point that you would like to filter", Type:=8)
    If rng = False Then Exit Sub
    rownow = rng.Row
    columnnow = rng.Column
    newrow = 9
    newcolumn = 1
    mytime = 0
    While Cells(rownow, columnnow).Value <> ""
        If Cells(rownow, columnnow).Value > 18 Or Cells(rownow, columnnow).Value <= 0 Or Abs(Cells(rownow + 1, columnnow - 1) - Cells(rownow, columnnow - 1)) > 0.2 Then
        mya = myb
        Else
            Cells(newrow, newcolumn).Value = mytime
            Cells(newrow, newcolumn + 1).Value = Cells(rownow, columnnow - 1).Value
            Cells(newrow, newcolumn + 4).Value = Cells(rownow, columnnow).Value
            newrow = newrow + 1
        End If
        rownow = rownow + 1
        mytime = mytime + 1
    Wend
End Sub

 

[Chris Mack]

Try amending the fifth line to:

rownow = Range(rng).Row

I'd also remove the "Type:=8". It seems to work better without it for me, even though type 8 is defined as "A cell reference, as a Range object."

Hope this helps,

Chris.

 

[VoG]

Perhaps like this

    Dim rng As Range
    Set rng = Application.InputBox("Select the first velocity data point that you would like to filter", Type:=8)
    If rng Is Nothing Then Exit Sub

 

[eulerddx4]

I just got Error 1004
Method 'Range of object'_Global' failed

 

[Chris Mack]

Remove "Type:=8".

 

[VoG]

I just got Error 1004
Method 'Range of object'_Global' failed

Did you try my suggestion?

 

[eulerddx4]

Ok so I took out

rownow = Range(rng).Row

and changed that line to my original code

Then I added

 Dim rng As Range
    Set rng = Application.InputBox("Select the first velocity data point that you would like to filter", Type:=8)
    If rng Is Nothing Then Exit Sub

And it worked perfectly!

Thanks guys!

 

[eulerddx4]

My final code is below, I didn't remove Type:=8 because I didn't see that until after I started playing with it.

I entered this code and the program works:

Sub mypicker()
    Dim rng As Range
    Set rng = Application.InputBox("Select the first velocity data point that you would like to filter", Type:=8)
    If rng Is Nothing Then Exit Sub
    rownow = rng.Row
    columnnow = rng.Column
    newrow = 9
    newcolumn = 1
    mytime = 0
    While Cells(rownow, columnnow).Value <> ""
        If Cells(rownow, columnnow).Value > 18 Or Cells(rownow, columnnow).Value <= 0 Or Abs(Cells(rownow + 1, columnnow - 1) - Cells(rownow, columnnow - 1)) > 0.2 Then
        mya = myb
        Else
            Cells(newrow, newcolumn).Value = mytime
            Cells(newrow, newcolumn + 1).Value = Cells(rownow, columnnow - 1).Value
            Cells(newrow, newcolumn + 4).Value = Cells(rownow, columnnow).Value
            newrow = newrow + 1
        End If
        rownow = rownow + 1
        mytime = mytime + 1
    Wend
End Sub

I went back and removed type:=8 and it gives me an error. If this program works with the type:=8 then everything should be ok, right?

 

[Chris Mack]

I'd imagine so - they both work for me.

My method treats "rng" as a text string, which is why it required "Range(rng)" as opposed to just "rng".

Chris

 

[eulerddx4]

My code up there is working except the cancel button is not working. Any ideas?