Range through Inputbox
I don't think anything is really wrong except Excel doesn't like the Dim
statements. I got the code below to work by commenting out the DIM. I would
like to know the answer also. I often have to comment out the DIM statements
because I don't know what is the correct format to use.
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 = rngInput
Set destRng = rngOutput
End Sub
"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.
|