Inputbox in VBA

J

Jeff

Hello,

I have the following VBA macro:
" If WorksheetFunction.CountIf(Range("E394:E709"), Range("E900")) <> 1 Then
Range("E" & i).EntireRow.Delete
Else
Range("L" & i).Value = ans
End If
End If
Next i"
I would like to know if it is possible to have an inputbox in VBA that
would give me the option to the change this range "(Range("E394:E709").
Jeff
 
B

Bob Phillips

Jeff,

Something along the lines of

Set oRng = Range("E394:E709"),
On Error Resume Next
Set oRng2 = In putbox("Select a range or cancel to use " & _
oRng.Address(False,False), Type:=8)
On Eror Goto 0
If Not oRng2 Is N othing Then
Set oRng = oRng2
End If

Set If WorksheetFunction.CountIf(oRng,Range("E900")) <> 1 Then
Range("E" & i).EntireRow.Delete
Else
Range("L" & i).Value = ans
End If
End If
Next i

The user can select the range when the input box pops up or cancel out
 
J

Jeff

Good morning,

I have the following error message for "Type:=8".
"Compile Error:
Named Argument not found"
Can you help me ?
Jeff
 
D

Dave Peterson

This line:

Set oRng2 = In putbox("Select a range or cancel to use " & _
oRng.Address(False,False), Type:=8)

Should be:

Set oRng2 = application.Inputbox("Select a range or cancel to use " & _
oRng.Address(False,False), Type:=8)

If that doesn't fix all your problems, you may want to post back with the code
you're using.
 
B

Bob Phillips

sorry Jeff, missed the Application.

--

HTH

RP

Dave Peterson said:
This line:

Set oRng2 = In putbox("Select a range or cancel to use " & _
oRng.Address(False,False), Type:=8)

Should be:

Set oRng2 = application.Inputbox("Select a range or cancel to use " & _
oRng.Address(False,False), Type:=8)

If that doesn't fix all your problems, you may want to post back with the code
you're using.
Click to expand...
 
G

Gord Dibben

Also this line

If Not oRng2 Is N othing Then

should be........ If Not oRng2 Is Nothing Then

Gord Dibben Excel MVP
 
Top