Vba to manipulate table into new table

Hi Experts,

I need help to build a macro to convert the data in the 'Uber Spreadsheet' tab in to the 'Example of end results' tab.

I have put a worked example with a description of each of the required rows and cells, a summary of which is below:

For each row in the 'Uber Spreadsheet' there needs to be 3 rows added to a new tab and each row needs to have specific data as shown in the worked example.

Hopefully someone can help here.

Thanks,
Sahil

Not sure if this is what you want.
But maybe you want to try to run it if the result as you expected.

Please Login or Register  to view this content.

Hi karmapala,

Thanks for this!

One additional thing, apologies, my instructions were wrong. I need the values to sum up per day per store. So there will only ever be one set of the "SALES", "Delivery Fees", "Uber Services - NL 852 546722853 (e)" rows per store per day.

Are you able to help with this?

Thanks,
Sahil

Hi karmapala,

This hasn't worked, see the attached spreadsheet for the result when you run this code.

The debug highlights sumOU = Application.Sum(rg.SpecialCells(xlCellTypeVisible).Offset(0, 28)) as the error.

Do you have any other ideas?

Thanks,
Sahil

Hi Sahil,
That strange, because in my side, it runs till the end of sub.

I downloaded your attachment,
then run it without error as seen in the image below :
2021-09-17_08-59-51.gif

Hi karmapala,

I am still getting the same issue, very strange.

When I run the code it says 'No cells were found' so I think the filtering might be changing something. Can you send me the file you are using so I can see if anything has changed?

Attachment 748058

THanks,
Sahil

Hi Sahil,

Attached is the same file uploaded by you on post #5,
but the sheet "result" already has data coming from running the macro.


Did the error happen before writing any data to sheet "result" ?
Or it has the chance to write some data, but in the middle of the loop it throw you the error ?

Please try to debug the code line via F8.

Hi karmapala,

Three line of incomplete data is written. Columns A to F is written but nothing else.

Could this be an issue as we using different versions of excel?

Sahil

Have you tried to debug (F8) it and watch the Locals Window while you debug it ?

If the Local Windows is not there in VB Editor, show it via :
View ---> Locals Window
The Locals window will appear.

Then put a stopper in this line :
EXCEL_2021-09-24_15-28-30.png

Then debug it by pressing F8 one at a time,
and see what happen in the Locals window.

Once the yellow pass the "sumOU = ..." line,
in the Locals window you should see sumOU 5.04
If not, then there is something wrong.

Assumed that you see sumOU 5.04 in the Locals window,
continue press F8...

Once the yellow pass the "oStart.Offset(0, 7).Value = ..."
On the sheet you should see 5.04 in cell H3.

Continue to do like that (press F8 slowly one at a time then watch the Locals window),
until the end of the sub.

Once you noticed the Locals window doesn't give any result
(once the yellow past the sumOU, sumIncS, sumIncU, sumVat etc)
and/or it doesn't write the result to the sheet
(once the yellow past the "oStart.Offset(...,...).value= ..."
Please write down what is the current el value in the Locals window
which doesn't give any result value to the sum and/or to the writing of the result to the sheet.

Could this be an issue as we using different versions of excel?

I don't know that.
In my side the code runs till the end without error,
where the result as you see in the attached workbook on post #8.

Hi karmapala,

When I run through the code the sumOU is 'Empty' in the local window as shown in the screenshot at the "sumOU = Application.Sum...." line of code.
Attachment 749270

Then when I press F8 again, the data in the local window disappears and I get the same "No cells were found" error. Screenshot below.Attachment 749271

Sahil1991SP,
Try the attached and see if it is how you wanted.

Please Login or Register  to view this content.

Hi jindon,

Apologies I missed the notification for your reply.

Your code almost works perfectly apart from one point:

- In the 'myresults' tab in the 'Marks' column the labelling system isn't working as expected. I see 'CC', 'CG' and 'CH' which are not the correct store lettering. Each store has a specific letter (see below) that needs to be added to the 'Marks' column. This letter is based on column C in the 'Uber Spreadsheet' tab. There are store names in brackets in column C and if the store is named then the corresponding letter needs to be shown in the 'Marks' column in the 'myresult' tab:

Greengates = r
Shipley = l
Crossgates = xg
Starbeck = st
Dewsbury =db
Hunslet =hl

Are you able to help me with that?

Change to

Please Login or Register  to view this content.

If you want to summarize the data, I need to see your desired result.

Hi jindon,

Apologies for the late reply, I didn't get a notification for your response.

Please see attached an example of what the final output should look like, see the 'Example of output' tab. I have colour coded the days from the output of the current code to show how these need to change, I hope that makes sense.

Thanks,
Sahil

See if this is how you wanted,

Figures are different from yours.

Please Login or Register  to view this content.

Hi jindon,

The latest change seems to have created a few errors in the calculations.

I have attached another spreadsheet with what the values should be and added comments for which cells they refer to in the data tab.
I have also noticed that there are several date-store combinations missing and I have added the list of these in the 'MMTemplate' tab in column N. I think because some date-store combinations are missing, the figures which should have been associated with them are being included in the wrong date-store.

Please let me know if you find a way to resolve this.

Thanks,
Sahil

Change 5 to 6 in 2nd loop in GetSummarize,

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Hi jindon, sintek,

Thanks for your inputs, I managed to get it to work!

Thanks,
Sahil