MazExpress
Board Regular
- Joined
- Aug 5, 2020
- Messages
- 56
- Office Version
- 2010
- Platform
- 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
Any help would be much appreciated.
Thanks in advance for anyone.
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.