PDA

View Full Version : problem with subtract



sbsite
10-31-2008, 11:56 AM
Hi Every one
There is a problem in my vba code and i don't find him ? :banghead:
all the details in the attached file !!

rbrhodes
10-31-2008, 03:33 PM
Hi sbsite,

There is an error in the arithmetic. (?) I couldn't figure it out but if you round it two 2 decimals it appears to work:


Private Sub CommandButton2_Click()

'Clear old
Range("I2:H65536").ClearContents

Range("A2").Select
'num = Selection.Value'
Do Until Selection.Value = ""
j = ActiveCell.Row
j = j + 1
If (Selection.Value = Cells(j, "a")) Then
' num = Selection.Value'
i = ActiveCell.Row
i = i + 1

'//Rounded to 2 decimals
If Cells(i, "e") <> Round(Cells(ActiveCell.Row, "e") - Cells(i, "f"), 2) Then
Cells(i, "h") = Selection.Value
Cells(i, "i") = "xxxx"
End If
End If
Selection.Offset(1, 0).Select
'num = Selection.Value'
Loop
Range("A2").Select
End Sub

mdmackillop
10-31-2008, 05:26 PM
Add the extra line here to demonstrate the rounding error
If (Cells(i, "e") <> Cells(ActiveCell.Row, "e") - Cells(i, "f")) Then
'Write difference to Col P
Cells(i, "p") = (Cells(i, "e") - Cells(ActiveCell.Row, "e") + Cells(i, "f"))
Cells(i, "h") = Selection.Value
Cells(i, "i") = "xxxx"
End If

mdmackillop
10-31-2008, 05:56 PM
BTW, best to avoid Selecting cells and unnecessary variables
Sub test()
Dim Cel As Range
For Each Cel In Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))
With Cel
If .Value = .Offset(1) Then
If .Offset(1, 4) <> .Offset(, 4) - Cel.Offset(1, 5) Then
'Show difference
.Offset(1, 14) = .Offset(1, 4) - .Offset(, 4) + .Offset(1, 5)
.Offset(1, 7) = .Value
.Offset(1, 8) = "xxxx"
End If
End If
End With
Next
End Sub