still having a prob w/ my code

Posted by Jim on January 11, 2002 7:51 AM

Hi group,

I'm trying to subtract G51 by the amount entered in
G52 "only" if E52 matches E51 and I can accomplish this
w/ a commandbutton But I want this to work without
the commandbutton. I've tried Private Sub Worksheet_
Change(ByVal Target as Range) and it returns the value
i.e. if G51 has 100 in the cell and I enter 10 in cell
G52 i get -2,100 not the correct ans. "90"
My code below:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("E52") = Range ("E51") Then
Range("G51") = Range("G51") - Range("G52")
End If
End Sub

Any ideas would be appreciated

Jim

Posted by Gregc on January 11, 2002 8:33 AM

Hope this helps

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 52 And Target.Column = 7 Then
If Range("G52") = Range("G51") Then
Range("G51") = Range("G51") - Range("G52")
End If
End If

Posted by Jim on January 11, 2002 9:11 AM

Thanks Gregc, Jim(nt)

That shouldn't happen. VBA is in English only, Excel however has different language versions. The only problem i've seen (I work with spanish and english versions) is when working with Data validation and Conditional formatting, or using formulas from the Analysis Toolpak, is this the case ?

Juan Pablo G.

Posted by Jim on January 11, 2002 10:20 AM

Posted by Tom Urtis on January 11, 2002 11:27 AM

Is this what you want?

I wasn't sure which cell (E51 or E52) was the one being changed but this code will handle both possibilities, unless the change is caused by a formula in those cells.

If the value of either E51 or E52 changes due to manual entry, this should work:

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
Case "$E$51", "$E$52"
If Range("E51").Value = Range("E52").Value Then
Range("G51").Value = Range("G51").Value - Range("G52").Value
End If
Case Else
Exit Sub
End Select
End Sub

Tom Urtis