Range through Inputbox
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. |
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. |
Range through Inputbox
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 |
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. |
All times are GMT +1. The time now is 04:34 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com