Solved: Use input box in a formula

[Lartk]

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]

In the sheet concerned's code-module try:[VBA]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Cll As Range
Set Cll = Intersect(Target, Range("D28"))
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
[/VBA]which is what's in the attached.

[Lartk]

I tried this but it doesnt work. Do I need for do something else for the code to work?

[VBA]Sub Test()
Dim Cll As Range
Set Cll = Intersect(Target, Range("D28"))
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[/VBA]

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

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

[p45cal]

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.[VBA]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
[/VBA]where the last row is the same as the last row in column A

[Lartk]

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]

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.

[VBA]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
[/VBA]

[p45cal]

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.

[VBA]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
[/VBA]

Seems fine to me. Scrub th earlier code from the sheet's code-module and have something like this in a standard code-module:[VBA]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
[/VBA]

[Lartk]

[VBA]

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

[/VBA]

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]

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

[Lartk]

That works, thanks!