CODEWITHSUNDEEP
excel
Ashok kumar
Ashok kumar

Reputation: 1611

How to do Calculation in Excel Cell upon leaving the cell

I work on Excel-2010 and Excel-2013. I want to do something like below:

Whatever the number I enter in cell, that number must be devided by 60 and the result should be printed on the same cell upon pressing TAB. If I come back to this cell, the entered number should be appeared, not the result of the calculation.

I don't know much about programming in Excel. I can't open most of the sites due to company security policies.

Can anyone please help me on this!

Upvotes: 2

Views: 141

Answers (1)

Kresimir L.
Kresimir L.

Reputation: 2441

Please use this code. You should paste this code in your Worksheet module, not Regular module. Then please adjust Range("A1:A100") to range in which numbers should be divided.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim DivRg As Range
Set DivRg = Range("A1:A100")
Set DivRg = Application.Intersect(Target, DivRg)

If DivRg Is Nothing Or Target.Cells.Count > 1 Then Exit Sub

Application.EnableEvents = False
Target = Target / 60
Target.Offset(0, 5).Value = 1
Application.EnableEvents = True

Set DivRg = Nothing

End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim DivRg As Range

Set DivRg = Range("A1:A100")
Set DivRg = Application.Intersect(Target, DivRg)

If DivRg Is Nothing Or Target.Cells.Count > 1 Then Exit Sub

Application.EnableEvents = False
If Target.Offset(0, 5).Value = 1 Then
Target = Target * 60
Target.Offset(0, 5).Value = 0
End If
Application.EnableEvents = True

Set DivRg = Nothing
End Sub

To accomplish your task, you will need one helper column (I chose column F) but you can choose any column and change this part of code to address that column Target.Offset(0, 5).Value. For example, if you want helper column to be column E , then use this code Target.Offset(0, 4).Value . Make sure you replace this code in all 3 parts of subs.

Upvotes: 1

Related Questions