Add a number to a selected range

[MazExpress]

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

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.

 

[AlanY]

try this

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

 

[MazExpress]

try this

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.

 

[AlanY]

Thank you so much. It worked.

you're welcome