VBA Code giving inconsistent results

[dddddmex]

Hello everybody, I have been struggling for a while with a Macro I'm trying to build; I've been stuck for 2 days, searching for a solution in the web and have not find success. Although for the last several times I've run the code (directly in the VBA Window) I keep getting different results, with the code and the database unchanged.

The code (at the end of the post) gathers information from a database (located in Sheet "Lista"), which has information in different columns such as Date, Family of product, product description (column F), unitary price (column G), units sold (column H), exchange rate (column I), sale in MXN (which is the product of the last three 3 columns; column J), and so on. First and foremost the code runs a Sub called ordenAlfab(), which simply sorts the product description column in Alfabetic order, and all the other columns in the dataset accordingly. The code of such Sub is the following:

Please Login or Register  to view this content.

After sorting, the main code will search for the duplicates of the product, sum the sales per unit in MXN and the units sold, and calculate a percentage of the sale of each product in comparison to the total amount of sales. All this new information is pasted and calculated in the Sheet "Reporte". Lastly, the list generated in "Reporte" will sort the column of % of total sales from Largest to Smallest (and all the other columns accordingly).

Every time I run the code I get different results. Sometimes I get the correct results, sometimes the % of total sales calculations are done incorrectly, sometimes the numbers are correct but the final dataset is not being sorted from largest to smallest, and so on.

Any idea of why this inconsistency is happening? Also, if you guys have any suggestions for changing the code for any better solutions, such suggestions are also welcomed.

Thanks a lot in advance, have a great day!

Please Login or Register  to view this content.

[zzzeddy]

Hi

It would really help if you attached a small sample file with some sample data.
Doesn't need to be 'real' data - you could put dummy values in, dummy names etc etc etc

Otherwise it would take a long time to create a test file.

zeddy

[dddddmex]

Thanks for your reply zeddy,

I uploaded the Sample File in the original post,

Thanks a lot!

[Marc L]

Hi,

in the sample file sheet "Reporte" is the correct expected result according to sheet "Lista" ?

[dddddmex]

Hi Marc,

Yes, that is the correct result, I want to have a summary list in "Reporte" without duplicates. Where there are duplicates in "Lista", sum of such duplicates will be dome in the fields of sales and units sold. Afterwards, the list in "Reporte" will be sorted from Largest to Smallest, and in the last column I want to show the % of such sale in relation to the total amount of sales.

The thing is my code already does this, nevertheless sometimes it makes the correct calculations and some other times it runs and does something differently; either it fails to sort properly, or it does the calculations incorrectly. It is the inconsistency of the code that I need to avoid.

I hope I made myself clear,

Thanks again, have a great day.

[Marc L]

According to your data I can't reproduce the same result using Excel formulas like any Excel beginner operating manually, should be easy.
Your result seems wrong as the sales total is different than the source sales total or you very had forgotten to explain your calculation …

[Marc L]

According to Excel basics, no need to sort the source data on column 'Descripción' - useless - like any loop is not needed …
The list without duplicates can be created via a single codeline with an advanced filter like any Excel beginner can operate manually.
One issue comes from your data with a line feed character - like a carriage return - at the end of a description (cell F23) …
Another from your code is the use of On Error Resume Next statement without any control
and with a VBA procedure respecting Excel rules for a such easy sum calculation
that's totally useless and inconsistent code means code to the trash !

Starting from Excel formulas, once the expected result is achieved, automatizing the same formulas even under VBA
- as result can overhide formulas - makes the procedure easier and consistent : each time I launch it
the result is always the same and the result sales total is always equal to the source sales total …