Selecting a Range using Inputbox Method

[John Pierce]

The following procedure works perfectly when the commented line is used
but when I try to use the Inputbox I get an error: "Ojbect required."
It turns out that the list may not start at the same place everytime
and there may be blanks or gaps. I tried to learn about the Inputbox
Method from the sample code at the bottom, which is from a book, but I
can't get it to work either. Any help would be appreciated.

Public Sub RunReports()
Dim vManagers As Range, vManager As Range, DataArray(5)
Windows("DataTable.xls").Activate
'Set vManagers = Range(Cells(23, "D"), Cells(Rows.Count,
"D").End(xlUp))
Set vManagers = Application.InputBox("Use the mouse to select all
manager names in Column D", "Select Range", , , , , , 8)
On Error Resume Next
For Each vManager In vManagers
If vManager.Value = "S. O' Neil" Then
vManager.Activate
DataArray(1) = ActiveCell.Offset(0, -3)
DataArray(2) = ActiveCell.Offset(0, -1)
DataArray(3) = ActiveCell.Offset(0, 1)
DataArray(4) = ActiveCell.Offset(0, 3)
DataArray(5) = ActiveCell.Offset(0, 7)
Windows("Check Deposit Report.xls").Activate
Sheets("CHECK-DEPOSIT").Copy After:=Sheets(1)
ActiveSheet.Unprotect
Range("F43").Value = DataArray(1)
Range("B43").Value = DataArray(2)
Range("F32").Value = DataArray(3)
Range("A43").Value = DataArray(4)
Range("I27").Value = -DataArray(5)
Windows("DataTable.xls").Activate
End If
Next
Windows("Check Deposit Report.xls").Activate
End Sub

Sub GetRange()
Dim UserRange As Range
Default = Selection.Address 'Assignment to constant not permitted.
On Error GoTo Canceled
Set UserRange = Application.InputBox(prompt:="Range to erase:",
Title:="Range Erase", Default:=Default, Type:=8)
UserRange.Clear
UserRange.Select
Canceled:
End Sub

 

[Bob Phillips]

Is that if you Cancel?

If so try

On Error Resume Next
Set vManagers = Application.InputBox("Use the mouse to select all
manager names in Column D", "Select Range", , , , , , 8)
On Error GoTo 0
If Not vManagers Is Nothing Then
For Each vManager In vManagers

 

[John Pierce]

Bob,
Thanks for the help. The problem wasn't on cancel but I put you code in
my procedure anyway and it works. The difference seems to be the Error
handling, something I'm not very good at. I saw some code recently where
at the end the variables were set to Nothing. When I test this thing
over and over it seems that it gets confused somewhat. Should I clear my
variables and array, and if so how?

 

[Bob Phillips]

John,

there are differing views on this. I personally don't bother unless I have a
big app and they become redundant early, then I do it to conserve memory. I
am firmly of the belief that they clear at the end themselves, and I have
had discussions with people on these boards about it (you can read one here
http://tinyurl.com/azq7r if you are interested). People who maintain you
should will clear object variables, but you don't see them clearing string
variables, or even arrays!

But, conversely, it never hurts to unset them.