Inputing data ranges

  • Thread starter Thread starter Ron Allred
  • Start date Start date
R

Ron Allred

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
 
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 (e-mail address removed)
 
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)
 
Back
Top