Input Box

[chrisaw100]

Hi,

If I get a range from a user through an input box, am I able to parse out the range? I would like to loop through it and multiply current cell values by a percentage.

Here's my code to get the range from the user:

Set rRange = Application.InputBox(Prompt:= _
"Please select a range with your Mouse.", _
Title:="SPECIFY RANGE", Type:=8)


Thanks,

Chris

 

[shg]

Welcome to the board.

One way:

    On Error Resume Next
    Set rRange = Application.InputBox(Prompt:="Please select a range with your Mouse.", _
                                      Title:="SPECIFY RANGE", _
                                      Type:=8)
    On Error GoTo 0
    If rRange Is Nothing Then Exit Sub
    
    For Each cell In rRange
        If VarType(cell.Value) = vbDouble Then
            cell.Value = cell.Value * 1.01
        End If
    Next cell

 

[Smitty]

Welcome to the Board!

Here's one way:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> foo()<br>    <SPAN style="color:#00007F">Dim</SPAN> rRange <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    <SPAN style="color:#00007F">Set</SPAN> rRange = Application.InputBox(Prompt:= _<br>        "Please select a range with your Mouse.", _<br>        Title:="SPECIFY RANGE", Type:=8)<br>        <br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Range(rRange.Address)<br>            c.Value = c.Value * 1.1<br>        <SPAN style="color:#00007F">Next</SPAN> c<br>        <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

HTH,

 

[jim may]

Still another way....

Sub Foo()
Set rRange = Application.InputBox(Prompt:= _
"Please select a range with your Mouse.", _
Title:="SPECIFY RANGE", Type:=8)
m = Val(InputBox("Enter % (using a leading decimal, like .5 for 50%"))
Range("Z1").Value = m   ' Cell Z1 used here as temporary cell to house % multiple
Range("Z1").Copy
rRange.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
Range("Z1").Value = ""
End Sub

 

[chrisaw100]

Thanks for your help!