Add a number to a selected range

MazExpress

Board Regular
Joined
Aug 5, 2020
Messages
56
Office Version
  1. 2010
Platform
  1. Windows
Hello all,
I want the user of my worksheet, via input box prompt, to both select a range of cells and a number to be added to that range.

I'm not a vba expert at all, but I could collect these pieces of codes from many sources to achieve my goal.

And because of not being an expert, I'm stuck at this error message:
Run-time error '1004:
Method 'Range of object_Worksheet' failed

With this line highlighted
newValue = myVal + ws.Range("DefaultRange")
The code is
VBA Code:
Sub Add_number_to_range()

Dim ws As Worksheet
Dim rng As Range
Dim DefaultRange As Range
Dim FormatRuleInput As String

Set ws = ActiveSheet

'Determine a default range based on user's Selection
  If TypeName(Selection) = "Range" Then
    Set DefaultRange = Selection
  Else
    Set DefaultRange = ActiveCell
  End If

'Get A Cell Address From The User to Get Number Format From
  On Error Resume Next
    Set rng = Application.InputBox( _
      Prompt:="Select a cell range", _
      Default:=DefaultRange.Address, _
      Type:=8)
  On Error GoTo 0

'Test to ensure User Did not cancel
  If rng Is Nothing Then Exit Sub

'Get the number from the user
Dim myVal As Variant
myVal = InputBox("The Number you wand to add is")

'Add the number to the pre-selected range
For Each newValue In DefaultRange
newValue = myVal + ws.Range("DefaultRange")
Next newValue
End Sub

Any help would be much appreciated.
Thanks in advance for anyone.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
try this

VBA Code:
Sub AddToRange()
    Dim myRange As Range
    Dim myValue As Double
    Dim myCell As Range

    'Prompt the user to select a range
    Set myRange = Application.InputBox("Select a range to add to", Type:=8)

    'Prompt the user to enter a number to add to the range
    myValue = Application.InputBox("Enter a number to add to the selected range")

    'Add the value to each cell in the selected range, if the cell contains a numeric value
    For Each myCell In myRange
        If VarType(myCell.Value) = vbDouble Or VarType(myCell.Value) = vbInteger Then
            myCell.Value = myCell.Value + myValue
        End If
    Next myCell
End Sub
 
Upvote 0
Solution
try this

VBA Code:
Sub AddToRange()
    Dim myRange As Range
    Dim myValue As Double
    Dim myCell As Range

    'Prompt the user to select a range
    Set myRange = Application.InputBox("Select a range to add to", Type:=8)

    'Prompt the user to enter a number to add to the range
    myValue = Application.InputBox("Enter a number to add to the selected range")

    'Add the value to each cell in the selected range, if the cell contains a numeric value
    For Each myCell In myRange
        If VarType(myCell.Value) = vbDouble Or VarType(myCell.Value) = vbInteger Then
            myCell.Value = myCell.Value + myValue
        End If
    Next myCell
End Sub
Thank you so much. It worked.
 
Upvote 0

Forum statistics

Threads
1,223,234
Messages
6,170,891
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top