alliswell
Board Regular
- Joined
- Mar 16, 2020
- Messages
- 190
- Office Version
- 2007
- Platform
- Windows
- Mobile
I want is, if A1 and b1 has value then C1 must get value by multiplying A1 and b1 and after clearing A1:C1, if A1 and C1 has value then then b1 must get value by dividing A1 by C1 and after clearing A1: C1, if B1 and C1 has value then A1 must get value By dividing B1 by C1.
Macro(....this macro working smooth )
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, rNums As Range
Dim c As Long
Set rng = Range("A1:C1")
If Not Intersect(Target, rng) Is Nothing Then
On Error Resume Next
Set rNums = rng.SpecialCells(xlConstants, xlNumbers)
On Error GoTo 0
If Not rNums Is Nothing Then
If rNums.Count = 2 Then
Application.EnableEvents = False
c = IIf(rNums.Areas.Count = 1, (2 * rNums.Column + 1) Mod 4, 2)
If c = 3 Then
rng.Cells(c).Value = rng.Cells(1).Value * rng.Cells(2).Value
Else
rng.Cells(c).Value = rng.Cells(3).Value / rng.Cells(3 - c).Value
End If
Application.EnableEvents = True
End If
End If
End If
End Sub
Peter u r simply great ! And i knew that Peter will be the one who will reply. I was confident. Peter ur macro worked as i needed. Just i need some changes.
I need flexibility in it.
If value A1 and b1 given C1 must get changed. Excel must understand that without clearing cell if I change values in A1 and B1 it shud update C1. Like wise if A1 and C1 has value then Excel must understand that B1 must change even if I change values in A1 and C1. I don't need to clear again and again. Whichever cell is empty it must understand what result must come in empty cell i. And the other thing is that I need this to get in whole A B & C column not only in A1:C1. For example if I m working on 5th row in A B C column the macro must work. If in 27th of row 27th row macro must work as it worked in A1:C1. and love u a lot bro.
Hope u understood what I need !
Thanks
Macro(....this macro working smooth )
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, rNums As Range
Dim c As Long
Set rng = Range("A1:C1")
If Not Intersect(Target, rng) Is Nothing Then
On Error Resume Next
Set rNums = rng.SpecialCells(xlConstants, xlNumbers)
On Error GoTo 0
If Not rNums Is Nothing Then
If rNums.Count = 2 Then
Application.EnableEvents = False
c = IIf(rNums.Areas.Count = 1, (2 * rNums.Column + 1) Mod 4, 2)
If c = 3 Then
rng.Cells(c).Value = rng.Cells(1).Value * rng.Cells(2).Value
Else
rng.Cells(c).Value = rng.Cells(3).Value / rng.Cells(3 - c).Value
End If
Application.EnableEvents = True
End If
End If
End If
End Sub
Peter u r simply great ! And i knew that Peter will be the one who will reply. I was confident. Peter ur macro worked as i needed. Just i need some changes.
I need flexibility in it.
If value A1 and b1 given C1 must get changed. Excel must understand that without clearing cell if I change values in A1 and B1 it shud update C1. Like wise if A1 and C1 has value then Excel must understand that B1 must change even if I change values in A1 and C1. I don't need to clear again and again. Whichever cell is empty it must understand what result must come in empty cell i. And the other thing is that I need this to get in whole A B & C column not only in A1:C1. For example if I m working on 5th row in A B C column the macro must work. If in 27th of row 27th row macro must work as it worked in A1:C1. and love u a lot bro.
Hope u understood what I need !
Thanks