PDA

View Full Version : Solved: Use input box in a formula



Lartk
06-11-2013, 09:46 AM
On the attached sheet, I am trying to incorporate a number that would be typed into an input box into a formula.

In cell D2, I would like the formula to be:

=B2-([enter number from input box]*C2)

Is there a way to have an input box pop up, I can enter a number, say 10, into the input box and then from there have that number be entered in the excel formula?

p45cal
06-11-2013, 11:25 AM
In the sheet concerned's code-module try:Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Cll As Range
Set Cll = Intersect(Target, Range("D2:D8"))
If Not Cll Is Nothing Then
If Cll.Cells.Count = 1 Then
x = InputBox("Enter a number")
If IsNumeric(x) Then
Cll.FormulaR1C1 = "=RC[-2]-" & x & "-RC[-1]"
End If
End If
End If
End Sub
which is what's in the attached.

Lartk
06-11-2013, 11:39 AM
I tried this but it doesnt work. Do I need for do something else for the code to work?

Sub Test()
Dim Cll As Range
Set Cll = Intersect(Target, Range("D2:D8"))
If Not Cll Is Nothing Then
If Cll.Cells.Count = 1 Then
x = InputBox("Enter a number")
If IsNumeric(x) Then
Cll.FormulaR1C1 = "=RC[-2]-" & x & "-RC[-1]"
End If
End If
End If
End Sub

Also, instead of this range: Set Cll = Intersect(Target, Range("D2:D8"))

Can I have the range be for all cells in column D from row 2 to lastrow?

p45cal
06-11-2013, 11:50 AM
I tried this but it doesnt work. Do I need for do something else for the code to work?Did it work in the attached file? The macro is already in there.
If not, have you enabled macros?
Did you put the code in the sheet's code-module, or somewhere else?
You need to select a cell in the range D2:D8 for it to work.



Also, instead of this range: Set Cll = Intersect(Target, Range("D2:D8"))

Can I have the range be for all cells in column D from row 2 to lastrow?Yes.Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Cll As Range
LR = Cells(Rows.Count, "A").End(xlUp).Row
Set Cll = Intersect(Target, Range("D2:D" & LR))
If Not Cll Is Nothing Then
If Cll.Cells.Count = 1 Then
x = InputBox("Enter a number")
If IsNumeric(x) Then
Cll.FormulaR1C1 = "=RC[-2]-" & x & "-RC[-1]"
End If
End If
End If
End Sub
where the last row is the same as the last row in column A

Lartk
06-11-2013, 12:04 PM
Oh I see it is already in the sheet. What if I dont want it in the sheet, I just want it to be part of a larger code that I kick off?

Lartk
06-11-2013, 12:57 PM
This code works however, it makes me enter a new input number everytime it goes to the "next i". Is there a way to say for all cells in range D2 to lastrow, use the number that same number that was firt inputted so i dont have to enter the input number for each cell.

Sub testt()
'
With ActiveSheet

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To Lastrow

x = InputBox("Enter a number")

If IsNumeric(x) Then

.Cells(i, "D").Select
ActiveCell.FormulaR1C1 = "=RC[-2]-" & x & "-RC[-1]"

End If
Next i
End With

End Sub

p45cal
06-11-2013, 01:16 PM
This code works however, it makes me enter a new input number everytime it goes to the "next i". Is there a way to say for all cells in range D2 to lastrow, use the number that same number that was firt inputted so i dont have to enter the input number for each cell.

Sub testt()
'
With ActiveSheet

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To Lastrow

x = InputBox("Enter a number")

If IsNumeric(x) Then

.Cells(i, "D").Select
ActiveCell.FormulaR1C1 = "=RC[-2]-" & x & "-RC[-1]"

End If
Next i
End With

End Sub
Seems fine to me. Scrub th earlier code from the sheet's code-module and have something like this in a standard code-module:Sub testt()
With ActiveSheet
Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To Lastrow
.Cells(i, "D").Select
x = InputBox("Enter a number for the selected cell")
If IsNumeric(x) Then ActiveCell.FormulaR1C1 = "=RC[-2]-" & x & "-RC[-1]"
Next i
End With
End Sub

Lartk
06-11-2013, 01:18 PM
Sub testt()
With ActiveSheet
Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To Lastrow
.Cells(i, "D").Select
x = InputBox("Enter a number for the selected cell")
If IsNumeric(x) Then ActiveCell.FormulaR1C1 = "=RC[-2]-" & x & "-RC[-1]"
Next i
End With
End Sub


This code still makes me enter an input number for each cell from D2 to lastrow. Is there a way to only enter a number in the input box once?

p45cal
06-11-2013, 01:20 PM
Sorry, didn't read the message properly:Sub testt()
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
x = InputBox("Enter a number ")
If IsNumeric(x) Then
Range("D2:D" & LastRow).FormulaR1C1 = "=RC[-2]-" & x & "-RC[-1]"
End If
End With
End Sub

Lartk
06-11-2013, 02:16 PM
That works, thanks!