vba help - substract not working

[Mallesh23]

Hi Team,

I am unable to substract column A value with Colum B Value properly.
why Both are Giving Either positive addition or Negative Addition.

Book3
	A	B	C	D	E
1	A	B	Diff		Expected Diff
2	5317.59	-5317.59	10635.18		0
3	5317.59	-5317.59	-10635.18		0
Sheet1

Cell Formulas
Range	Formula
C2	C2	=A2-B2
C3	C3	=B3-A3

Thanks
mg

 

[Fluff]

If you want to get the difference then add the values.
With that data
A2-B2 is the same as 5317.59 - -5317.59 which is 5317.59+5317.59

 

[joeu2004]

Why is the title "vba help..."? Your examples are Excel formulas.

Fluff already explained the major issue: add, instead of subtract.

But I wonder if you also need to explicitly round in order to avoid infinitesimal differences of the form 9.09E-13.

(We are probably unable to duplicate the rounding problem because XL2BB does not provide the exact binary values in column A and column B, which I assume are calculations in the actual Excel file.)

And yes, VBA and Excel results might differ in that respect, in part because of "tricks" that Excel plays.

In Excel, write:

=ROUND(A2+B2, 2)

In VBA, write:

Range("C2") = WorksheetFunction.Round(Range("A2") + Range("B2"), 2)

FYI, I use WorksheetFunction.Round instead of VBA Round because the latter rounds differently ("banker's rounding"). It might not make any difference in this case. But I eschew the use of VBA Round on principle.

 

[Mallesh23]

Hi Joeu and Fluff,

yes it should be excel formula help, I missed it,

and Thanks for your help, its working now. Thanks


Thanks
mg

 

[Fluff]

You're welcome & thanks for the feedback.