Maurice February 2016

Keep a fixed ratio between 2 cells Excel vba

Currently im making a model in which I need 2 cells to keep a fixed ratio such that when the sum off the 2 cells always equals one. In this case the cells are F13 and F14. To give an example if the value off F13 gets changed to 0.4 then F14 should become 0.6. Same goes if the value off F14 gets changed to 0.2 then F13 should become 0.8. The code I got so for to get is:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Target.Worksheet.Range("F13")) Is Nothing Then
        If ActiveCell.Value > 1 Then
            MsgBox "input value is larger than 1"
        Else
            Range("F14").Value = 1 - ActiveCell.Value
            Exit Sub
        End If
        Exit Sub
    End If
    Exit Sub

    If Not Intersect(Target, Target.Worksheet.Range("F14")) Is Nothing Then
        If ActiveCell.Value > 1 Then
            MsgBox "input value is larger than 1"
        Else
            Range("F13").Value = 1 - ActiveCell.Value
            Exit Sub
        End If
        Exit Sub
    End If
    Exit Sub

End Sub

However this code is not working properly. My question is then therefore how should I change my code in order to get this working properly?

Answers


John Coleman February 2016

Maybe this will work for you:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim v As Variant
    If Target.Address = "$F$13" Then
        v = Target.Value
        If Not IsNumeric(v) Then
            Range("F13").Value = CVErr(xlErrValue)
        ElseIf v < 0 Or v > 1 Then
            Range("F13").Value = CVErr(xlErrValue)
        Else
            Range("F14").Value = 1 - v
        End If
    ElseIf Target.Address = "$F$14" Then
        v = Target.Value
        If Not IsNumeric(v) Then
            Range("F14").Value = CVErr(xlErrValue)
        ElseIf v < 0 Or v > 1 Then
            Range("F14").Value = CVErr(xlErrValue)
        Else
            Range("F13").Value = 1 - v
        End If
    End If
End Sub

If F13 is changed to be a value between 0 and 1 then F14 is automatically changed to be the complementary value, and vice-versa. If any value other than a number between 0 and 1 is put in one of those 2 cells then the value in that cell becomes an error. The event handler only works if the value is put directly in either of those cells (as opposed to a larger range containing those cells) but with your use of ActiveCell that seems to be the intention.

Post Status

Asked in February 2016
Viewed 2,435 times
Voted 4
Answered 1 times

Search




Leave an answer