• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro required to auto change the results

nagovind

Member
Dear all


Equation is V = I x R

V is in A1, I is in B1 and R is in C1


Aim is if i give any one of the input in any one of the cell then the corresponding other 2 values has to be changed


Below code is not working please help


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

V = Range("A1")

I = Range("B1")

R = Range("C1")


'equation is V = I x R


If Target.Row = 1 And Target.Column = 1 Then


Range("B1") = V / R

Range("C1") = V / I

GoTo k

End If


If Target.Row = 1 And Target.Column = 2 Then


Range("A1") = I * R

Range("C1") = V / I

GoTo k

End If


If Target.Row = 1 And Target.Column = 3 Then


Range("B1") = V / R

Range("A1") = I * R

Exit Sub

End If


k:

End Sub
 

Hui

Excel Ninja
Staff member
@Nagovind

You have 2 problems here

Your code is looping as you are not controlling the updating

That is as soon as you change 1 cell it refires the worksheet change event and it keeps looping.

This is fixed using the EnableEvents handler, see below

[pre]
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

'Equation is V = I x R
V = Range("A1")
I = Range("B1")
R = Range("C1")

If Target.Row = 1 And Target.Column = 1 Then
Range("B1") = V / R
Range("C1") = V / I
GoTo k
ElseIf Target.Row = 1 And Target.Column = 2 Then
Range("A1") = I * R
Range("C1") = V / I
GoTo k
ElseIf Target.Row = 1 And Target.Column = 3 Then
Range("B1") = V / R
Range("A1") = I * R
GoTo k
End If

k:
Application.EnableEvents = True

End Sub

The second problem is that your logic is wrong

Your equation V=IR is correct

eg: 24=6 x 4

if we change the 6 to an 8

your code is trying to change 2 variables

First it recalculates V and then R

by :

[pre][code]Range("A1") = I * R
Range("C1") = V / I
[/pre]
But these are both based on the old values of the V, I & R


As I understand electronics, in an electrical circuit your Resistance is fixed


Current

and so if you change the Current I, only the Voltage changes

so your equation should be Vnew = Target x Resistance


Voltage

and so if you change the Voltage V, only the Current changes

so your equation should be Inew = Target / Resistance


Resistance

and so if you change the Resistance R, only the Current changes (assumes your supplying a constant voltage)

so your equation should be Inew= Target x Resistance


and so your code should be

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

'Equation is V = I x R
V = Range("A1")
I = Range("B1")
R = Range("C1")

If Target.Row = 1 And Target.Column = 1 Then
Range("B1") = Target / R
' Range("C1") = V / I
GoTo k
ElseIf Target.Row = 1 And Target.Column = 2 Then
Range("A1") = Target * R
' Range("C1") = V / I
GoTo k
ElseIf Target.Row = 1 And Target.Column = 3 Then
' Range("B1") = V / R
Range("A1") = I * Target
GoTo k
End If

k:
Application.EnableEvents = True

End Sub[/code][/pre]
Now if my electrical understanding is wrong the above is incorrect
 

Hui

Excel Ninja
Staff member
@Nagovind

You have 2 problems here

Your code is looping as you are not controlling the updating

That is as soon as you change 1 cell it refires the worksheet change event and it keeps looping.

This is fixed using the EnableEvents handler, see below

[pre]
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

'Equation is V = I x R
V = Range("A1")
I = Range("B1")
R = Range("C1")

If Target.Row = 1 And Target.Column = 1 Then
Range("B1") = V / R
Range("C1") = V / I
GoTo k
ElseIf Target.Row = 1 And Target.Column = 2 Then
Range("A1") = I * R
Range("C1") = V / I
GoTo k
ElseIf Target.Row = 1 And Target.Column = 3 Then
Range("B1") = V / R
Range("A1") = I * R
GoTo k
End If

k:
Application.EnableEvents = True

End Sub

The second problem is that your logic is wrong

Your equation V=IR is correct

eg: 24=6 x 4

if we change the 6 to an 8

your code is trying to change 2 variables

First it recalculates V and then R

by :

[pre][code]Range("A1") = I * R
Range("C1") = V / I
[/pre]
But these are both based on the old values of the V, I & R


As I understand electronics, in an electrical circuit your Resistance is fixed


Current

and so if you change the Current I, only the Voltage changes

so your equation should be Vnew = Target x Resistance


Voltage

and so if you change the Voltage V, only the Current changes

so your equation should be Inew = Target / Resistance


Resistance

and so if you change the Resistance R, only the Current changes (assumes your supplying a constant voltage)

so your equation should be Inew= Target x Resistance


and so your code should be

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

'Equation is V = I x R
V = Range("A1")
I = Range("B1")
R = Range("C1")

If Target.Row = 1 And Target.Column = 1 Then
Range("B1") = Target / R
' Range("C1") = V / I
GoTo k
ElseIf Target.Row = 1 And Target.Column = 2 Then
Range("A1") = Target * R
' Range("C1") = V / I
GoTo k
ElseIf Target.Row = 1 And Target.Column = 3 Then
' Range("B1") = V / R
Range("A1") = I * Target
GoTo k
End If

k:
Application.EnableEvents = True

End Sub[/code][/pre]
Now if my electrical understanding is wrong the above is incorrect, but you'll be able to work out what it should be doing
 

nagovind

Member
Hui

Thanks...You are electrically 100% right

Actually i need to dynamically change the 3 variables and took some easy equation

But as per your logic it is working fine thanks


But my intention is to imitate like a web pages ...


but thanks i understood the logic
 
Top