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.

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.

Thank you! It works, but I have to comment Option Explicit too...

Regards
j.

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

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.