newbie VBA problem

[NleahciM]

Hi - as a test of an idea I was working on - I tried making a simple pounds <> kilograms converter. I had a feeling it was gonna have a problem though - and I was right.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target = Range("H4") Then
        Range("I4").Value = Range("H4").Value * 2.20462262
        Range("A2").Value = Range("A2").Value + 1 'demonstrating problem
    ElseIf Target = Range("I4") Then
        Range("H4").Value = Range("I4").Value / 2.20462262
        Range("A3").Value = Range("A3").Value + 1 'demonstrating problem
    End If
End Sub

So - the idea is that when you changed the value of one cell, it'd write in the appropriate amount in the other cell. Problem is - that Excel then registers this is a change, so runs the script again. Creating a loop. You can see I put loop counters in A2 and A3 - and they increment by about 100 each time I change a value. How do I fix this?

Sorry - I am more of a C guy.

Thanks!

PS is there an equivalent to C's += operator in VBA? I couldn't find one.

 

[NleahciM]

OK - I came up with something else that seems to *mostly* solve the problem:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
    Static PreviousCell As Range
    
    If PreviousCell = Range("H4") Then
        Range("I4").Value = Range("H4").Value * 2.20462262
    ElseIf PreviousCell = Range("I4") Then
        Range("H4").Value = Range("I4").Value / 2.20462262
    End If
    
    Set PreviousCell = Target
End Sub

Thing is - it only works most of the time - for some reason, sometimes it seems to get confused about what the last cell was or something. I don't get it. Any ideas?

 

[ppandemic]

Your second code snippet seems to work for me with Excel 2007 with all service packs.

Make sure you are using the latest service pack for your Excel.

If I could reproduce the problem, I could probably help you