Inputing data ranges
 

I need assistance. I am teaching myself some aspects of
VBA programming using Excel 2000. What I would like to do
is 1) input a range of data using the InputBox command; 2)
perform an operation on each cell within the given input
range. I have tried several times to write a simple set
of code to encompass the above functionality, but have not
had much success. Below is a recent attempt.

Sub conv2()
Dim cel As Range
Dim celobj
Dim NumberElements
cel = InputBox("Enter range (Ex. B2:B9)")
NumElements = UBound(cel) - LBound(cel) + 1
MsgBox "Number of elements is " & NumElements
For Each celobj In cel
If IsNumeric(myobj) Then
myobj.Value = myobj.Value + 1
End If
Next
End Sub

Any help would be most appreciated.

Thanks
Ron Allred

Inputing data ranges
 

Ron,

When you want the user to enter a range, it is a good idea to use
Excel's own InputBox method rather than the standard InputBox
function. The Type:=8 parameter indicates that the user is to
enter a range. E.g,


Dim BigRng As Range
Dim Rng As Range
On Error Resume Next
Set BigRng = Application.InputBox(Prompt:="Enter a range",
Type:=8)
On Error Goto 0
If BigRng Is Nothing Then
MsgBox "You didn't enter a range"
Else
For Each Rng In BigRng
Rng.Value = Rng.Value + 1
Next Rng
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Ron Allred" wrote in message
...
I need assistance. I am teaching myself some aspects of
VBA programming using Excel 2000. What I would like to do
is 1) input a range of data using the InputBox command; 2)
perform an operation on each cell within the given input
range. I have tried several times to write a simple set
of code to encompass the above functionality, but have not
had much success. Below is a recent attempt.

Sub conv2()
Dim cel As Range
Dim celobj
Dim NumberElements
cel = InputBox("Enter range (Ex. B2:B9)")
NumElements = UBound(cel) - LBound(cel) + 1
MsgBox "Number of elements is " & NumElements
For Each celobj In cel
If IsNumeric(myobj) Then
myobj.Value = myobj.Value + 1
End If
Next
End Sub

Any help would be most appreciated.

Thanks
Ron Allred

Inputing data ranges
 

Option Explicit
Sub conv2()
Dim cel As Range
Dim sCel As String
Dim celobj As Range
Dim NumElements As Long
Dim mysum As Double
sCel = InputBox("Enter range (Ex. B2:B9)")
On Error Resume Next
Set cel = Range(sCel)
On Error GoTo 0
If cel Is Nothing Then
MsgBox "Invalid range specification: " & sCel & _
vbNewLine & "Exiting . . . "
Exit Sub
End If
NumElements = cel.Count
MsgBox "Number of elements is " & NumElements
For Each celobj In cel
If IsNumeric(celobj) Then
mysum = celobj.Value + mysum
End If
Next celobj
MsgBox "sum is: " & mysum
End Sub


You can also use the Application.Inputbox with a type:=8 to select a range
with the mouse

Dim rng as Range
On Error Resume Next
set rng = Application.InputBox("Select a range", type:=8)
On Error goto 0
if rng is nothing then
msgbox "You hit cancel"
exit sub
End if
msgbox rng.address(external:=true)

--
Regards,
Tom Ogilvy



"Ron Allred" wrote in message
...
I need assistance. I am teaching myself some aspects of
VBA programming using Excel 2000. What I would like to do
is 1) input a range of data using the InputBox command; 2)
perform an operation on each cell within the given input
range. I have tried several times to write a simple set
of code to encompass the above functionality, but have not
had much success. Below is a recent attempt.

Sub conv2()
Dim cel As Range
Dim celobj
Dim NumberElements
cel = InputBox("Enter range (Ex. B2:B9)")
NumElements = UBound(cel) - LBound(cel) + 1
MsgBox "Number of elements is " & NumElements
For Each celobj In cel
If IsNumeric(myobj) Then
myobj.Value = myobj.Value + 1
End If
Next
End Sub

Any help would be most appreciated.

Thanks
Ron Allred