Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding input box number to range of cells values
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding input box number to range of cells values
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 we 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding input box number to range of cells values
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding input box number to range of cells values
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding values for all rows in a range | Excel Worksheet Functions | |||
Can I read a range of scenario (input) values from a table? | Excel Discussion (Misc queries) | |||
Trying to validate input based upon values in two other cells | Excel Discussion (Misc queries) | |||
Auto-adding text when a certain number is input | Excel Worksheet Functions | |||
adding values if the same - SUMIF in range of cells? | Excel Worksheet Functions |