Adding input box number to range of cells values

Greetings!

I have used the paste special technique before to add a value to a range of
selected values. What I would like to do now, is that same technique but
instead of choosing a cell value in the worksheet I would like to use an
input box for that value. What I can't figure out is how to copy that input
box value to be used in the paste special commands.

Below is what the code looks like if I type a value in a cell, select and
copy it, and then select my new range of cells to use the paste special
command to add that value to the existing numeric values.

'This is the number I am copying
Range("E2").Select
Selection.Copy
'Now I select the range of cells I want to add it too
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
'The paste special command to add that value to the existing numeric values
in the cells
Selection.PasteSpecial Paste:=xlAll, Operation:=xlAdd, SkipBlanks:=False _
, Transpose:=False


Any ideas on how to do this by using an input box?

Many thanks!

Jessica

Maybe...

Option Explicit
Sub testme()

Dim myStr As String
Dim myRngToFill As Range

myStr = InputBox(Prompt:="type something here!")

If myStr = "" Then
Exit Sub
End If

With ActiveSheet
Set myRngToFill = .Range("a2", .Range("a2").End(xlDown))
End With

myRngToFill.Value = myStr

End Sub

Jessica wrote:
>
> Greetings!
>
> I have used the paste special technique before to add a value to a range of
> selected values. What I would like to do now, is that same technique but
> instead of choosing a cell value in the worksheet I would like to use an
> input box for that value. What I can't figure out is how to copy that input
> box value to be used in the paste special commands.
>
> Below is what the code looks like if I type a value in a cell, select and
> copy it, and then select my new range of cells to use the paste special
> command to add that value to the existing numeric values.
>
> 'This is the number I am copying
> Range("E2").Select
> Selection.Copy
> 'Now I select the range of cells I want to add it too
> Range("A2").Select
> Range(Selection, Selection.End(xlDown)).Select
> 'The paste special command to add that value to the existing numeric values
> in the cells
> Selection.PasteSpecial Paste:=xlAll, Operation:=xlAdd, SkipBlanks:=False _
> , Transpose:=False
>
> Any ideas on how to do this by using an input box?
>
> Many thanks!
>
> Jessica

--

Dave Peterson

That placed the string value in the entire range rather than adding that
value to the values already in the cell. For example if the cells were:

1
12
5
8
1
3


and the input value was 200700, I would want the values to become:

200701
200712
200705
200708
200701
200703.

This code just made them all equal to 200700.

Jessica



"Dave Peterson" wrote:

> Maybe...
>
> Option Explicit
> Sub testme()
>
> Dim myStr As String
> Dim myRngToFill As Range
>
> myStr = InputBox(Prompt:="type something here!")
>
> If myStr = "" Then
> Exit Sub
> End If
>
> With ActiveSheet
> Set myRngToFill = .Range("a2", .Range("a2").End(xlDown))
> End With
>
> myRngToFill.Value = myStr
>
> End Sub
>
> Jessica wrote:
> >
> > Greetings!
> >
> > I have used the paste special technique before to add a value to a range of
> > selected values. What I would like to do now, is that same technique but
> > instead of choosing a cell value in the worksheet I would like to use an
> > input box for that value. What I can't figure out is how to copy that input
> > box value to be used in the paste special commands.
> >
> > Below is what the code looks like if I type a value in a cell, select and
> > copy it, and then select my new range of cells to use the paste special
> > command to add that value to the existing numeric values.
> >
> > 'This is the number I am copying
> > Range("E2").Select
> > Selection.Copy
> > 'Now I select the range of cells I want to add it too
> > Range("A2").Select
> > Range(Selection, Selection.End(xlDown)).Select
> > 'The paste special command to add that value to the existing numeric values
> > in the cells
> > Selection.PasteSpecial Paste:=xlAll, Operation:=xlAdd, SkipBlanks:=False _
> > , Transpose:=False
> >
> > Any ideas on how to do this by using an input box?
> >
> > Many thanks!
> >
> > Jessica
>
> --
>
> Dave Peterson
>

I think I have found a way to do this. See the following:

Sub testme()

Dim myStr As Long
Dim InputRng As Range
Dim cel As Range

myStr = InputBox(Prompt:="Enter year samples taken.")
myStr = myStr & "0000"

With ActiveSheet
Set InputRng = .Range("a2", .Range("a2").End(xlDown))
End With

For Each cel In InputRng
cel.Value = cel.Value + myStr
Next

End Sub














"Jessica" wrote:

> Greetings!
>
> I have used the paste special technique before to add a value to a range of
> selected values. What I would like to do now, is that same technique but
> instead of choosing a cell value in the worksheet I would like to use an
> input box for that value. What I can't figure out is how to copy that input
> box value to be used in the paste special commands.
>
> Below is what the code looks like if I type a value in a cell, select and
> copy it, and then select my new range of cells to use the paste special
> command to add that value to the existing numeric values.
>
> 'This is the number I am copying
> Range("E2").Select
> Selection.Copy
> 'Now I select the range of cells I want to add it too
> Range("A2").Select
> Range(Selection, Selection.End(xlDown)).Select
> 'The paste special command to add that value to the existing numeric values
> in the cells
> Selection.PasteSpecial Paste:=xlAll, Operation:=xlAdd, SkipBlanks:=False _
> , Transpose:=False
>
>
> Any ideas on how to do this by using an input box?
>
> Many thanks!
>
> Jessica
>

I'm not sure what you want:

cel.Value = cel.Value + myStr
or
cel.Value = cel.Value & myStr

You may see that one adds values and one concatenates strings.

Excel's VBA can be forgiving--but not always. I'd be careful.

Jessica wrote:
>
> I think I have found a way to do this. See the following:
>
> Sub testme()
>
> Dim myStr As Long
> Dim InputRng As Range
> Dim cel As Range
>
> myStr = InputBox(Prompt:="Enter year samples taken.")
> myStr = myStr & "0000"
>
> With ActiveSheet
> Set InputRng = .Range("a2", .Range("a2").End(xlDown))
> End With
>
> For Each cel In InputRng
> cel.Value = cel.Value + myStr
> Next
>
> End Sub
>
> "Jessica" wrote:
>
> > Greetings!
> >
> > I have used the paste special technique before to add a value to a range of
> > selected values. What I would like to do now, is that same technique but
> > instead of choosing a cell value in the worksheet I would like to use an
> > input box for that value. What I can't figure out is how to copy that input
> > box value to be used in the paste special commands.
> >
> > Below is what the code looks like if I type a value in a cell, select and
> > copy it, and then select my new range of cells to use the paste special
> > command to add that value to the existing numeric values.
> >
> > 'This is the number I am copying
> > Range("E2").Select
> > Selection.Copy
> > 'Now I select the range of cells I want to add it too
> > Range("A2").Select
> > Range(Selection, Selection.End(xlDown)).Select
> > 'The paste special command to add that value to the existing numeric values
> > in the cells
> > Selection.PasteSpecial Paste:=xlAll, Operation:=xlAdd, SkipBlanks:=False _
> > , Transpose:=False
> >
> >
> > Any ideas on how to do this by using an input box?
> >
> > Many thanks!
> >
> > Jessica
> >

--

Dave Peterson