Range through Inputbox
The problem is that in:
Dim rngInput As Range, rngOutput As Range
and
Set rngInput = Application.InputBox(msg1, Type:=8)
you are treating rngInput as a Range variable, but in:
Set Rng = SH.Range(rngInput)
you are using it as a String variable ! Consider:
Set Rng = SH.rngInput
--
Gary''s Student
gsnu200709
"jasmine" wrote:
Hi,
Why the following code produces an error?
Public Sub aSub()
Const msg1 = "Select input range"
Const msg2 = "Select output cell"
Dim WB As Workbook
Dim SH As Worksheet
Dim Rng As Range
Dim destRng As Range
Dim rngInput As Range, rngOutput As Range
Set rngInput = Application.InputBox(msg1, Type:=8)
Set rngOutput = Application.InputBox(msg2, Type:=8)
If rngInput Is Nothing Then Exit Sub
If rngOutput Is Nothing Then Exit Sub
Set WB = ActiveWorkbook
Set SH = WB.ActiveSheet
Set Rng = SH.Range(rngInput)
Set destRng = SH.Range(rngOutput)
End Sub
While if I change the last rows, for example
Set Rng=SH.Range("B1:C1000")
Set destRng = SH.Range("F2")
it works
I want to use the ranges selected with inputboxes, and not change the code!
Where is the mistake?
Thanks
j.
|