mACRO NOT CALCULATING DIFFERENCE BETWEEN SHEETS CORRECTLY

[gareth7412]

I have a macro that I inhereted in a workbook and its not working properly.

What its meant to do is compare figures between sheet called "TAB" and "WFD" and what ever the difference is it shows the data on the sheet called Sheet 3.

Take for instance the workbook enclosed. the value in TAB is -358.50 and the value in WFD is -379.10 of which is 20.60.

When I run the macro it comes up with a diff of 28.80. This is just a minor diff but when I run it with loads of data (lets say 6000 entries) The difference is massive,

Why is this, I know very little on macros so I have a cheek asking if you could amend the workbook that would be great.



THanks

[JohnTopley]

Data in Sheet3 does not appear to relate to data in TAB or WFD.

[Marc L]

And difficult to compare dates as Excel dates and dates as texts ! …

[Marc L]

Anyway according to the attachment to just calculate the difference between both sheets an Excel basics VBA demonstration :

Sub Demo1() 
    MsgBox "Difference  :  " & CCur(Application.Sum(Sheet1.UsedRange.Columns(4)) - Application.Sum(Sheet2.UsedRange.Columns(4))) 
End Sub  

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

[gareth7412]

Thanks Marc. What you done is correct to show what the difference is between the 2 sheets but im trying to find out what entries make up that difference.

It maybe in tab called Watford and not in tab called tableau and VV.

[Marc L]

If dates comparison is necessary so it works only with smart worksheets which is very not the case with your attachment …

[gareth7412]

All of columns A-D I need to compare

[Marc L]

So back to post #3 : why so many dates as texts in sheet WFD ?‼

As the code was not made for such dumb case …

[gareth7412]

When I download the file it downloads as a .CSV file and then I save as an excel workbook am I doing it wrong there?

I have now done a format on the cells

Select the first value in column D that show the pound sign
Right-click->Copy
Select the whole column
Right-click and under Paste Options choose Formatting which is the 5th symbol in

The issue I have now is tht in the watford tab the first 2 entries are nit changing and have tried to format cells but still no diff. This is how much im out by.

Any Ideas.

[Marc L]

Just a text file bad import settings !

Activate the Macro Recorder, import manually the text file and well answer to the Import Wizard
in order once imported the dates must be directly aligned to the right (numbers) rather than to the left (texts) …

Anyway according to your attachment Sheet3 result must only contain 20 missing rows on the WFD side.

[gareth7412]

just so im 100% correct i do the following..

Download the CSV file
click on transform data??
click on close and load??
save as a XLS file??

[Marc L]

If you well answer to the Import Wizard the dates are directly imported as Excel dates without any 'transform data' neither close etc …

Anyway according to your actual source data worksheets an attachment with the exact expected result should help so far
in order to replace your actual overcomplicated code with an Excel basics shorter - half size ! - VBA procedure
working even with such badly imported dates !