CODEWITHSUNDEEP
excelformula
kater4e
kater4e

Reputation: 1

Excel input and output in same cell

I'm not a programmer and I need some help!

I have an excel sheet where I want to have a formula in the background and when I input a number I want it to be calculated in the formula without deleting it in the same cell.

The formula is like this X*6.75%+X , where X is the input. Some help would be appreciated.

Thanks.

Upvotes: 0

Views: 5839

Answers (3)

cant code
cant code

Reputation: 1

Check this out:

Sub YourModuleName()
For Each cell In Application.Selection.Cells
cell.Formula = "=" + CStr(cell.Value) + "*" + "n5"
Next
End Sub

n5 is your editable cell

Now, you can highlight a range of cells and run the module to do your calculation

Upvotes: 0

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19837

You haven't tagged as requiring VBA, but that's the only way I can think of achieving this.
As @FoxfireAndBurnsAndBurns says - you'd have to update the formula each time without VBA.

For a VBA method you'd add this code to the specific worksheet module (updating the cell reference as required): 

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$A$1" Then
        If IsNumeric(Target) Then
            Application.EnableEvents = False
            'Target = Target * 6.75 / 100 + Target 'Place value in cell.
            Target.Formula = "=" & Target & "* 6.75 / 100 + " & Target 'Update formula in cell.
            Application.EnableEvents = True
        End If
    End If

End Sub

Edit: I've updated code to include suggestions in comments.

Upvotes: 2

Samuel Hulla
Samuel Hulla

Reputation: 7099

I don't see how this is better than simply using an extra column with formula, but...

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

  If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
  'fire only in column A

    If IsNumeric(Target.Value2) Then ' only makes sense to apply if it's a number _
    otherwise it would produce a Type mismatch error

        Application.EnableEvents = False 'to prevent constant looping on change
        Target = Target * 6.75 / 100 + Target
        Application.EnableEvents = True

    Else
         MsgBox ("I can only take numeric values")
    End If

End Sub

NOTE: It's important to put the code for Worksheet_Change Event inside the module of the respective Sheet Tab. eg. if you want to the formula to fire in your sheet that is named "Sheet1", you will need to make sure you have the module of Sheet1 selected:

enter image description here

Pasting this code anywhere else (eg. Sheet2, or Module 1) will not work!

Upvotes: 1

Related Questions