Macros: Data cleaning macros not producing expected outputes.

Originally Posted by bertlogdi1

What is wrong with my code?

It is not wrapped in code tags. Please do so, and then I will be better able to help you. See the Forum Rules on how to wrap your code in code tags.

Thanks for your advice. I have amended my post.
Rgds.

Originally Posted by Whizbang

It is not wrapped in code tags. Please do so, and then I will be better able to help you. See the Forum Rules on how to wrap your code in code tags.

Thank you Whizbang.
The first part works well once the first line range is amended for the missing colon. However the second one has not worked as it keeps complaining of this line:

Please Login or Register  to view this content.

Rgds.

[QUOTE=Whizbang;2539837]Ok, first we need to clean up your code. .Select is rarely needed in VBA. The recorder puts them there because, while you are recording, you are actually selecting cells. But, they aren't needed to do most operations. The code below does the same thing as your code, but with most of the Select statements removed. This will make the cleaner routines run much faster. There are a couple of them left, but I will post some code later that will remove them completely. I just wanted to take things one step at a time.

So, I am finally looking at your attached workbooks and running through your code (up until now I haven't been testing the code I provide. I've just been drafting it in Notepad++ and then pasting it here.). I had forgotten that Match() cannot be used to find blank cells.

So, here is a replacement for the Match() function method above. I know I promised the next code I provided would be a solution to your problems, but I figured I would address my own errors seperately.

Please Login or Register  to view this content.

Ok, now while running your code on the provided sample workbooks, I have come upt with a couple of questions/comments.

Why are you testing for the U column formula(s) to evaluate to six? Meaning, why copy data? It seems to me this is just to create filler rows. Why is this necessary? Why not just delete all blank rows?

This code:

Please Login or Register  to view this content.

doesn't seem to do anything. You have just filled the first 190 rows with a formula. So this line of code does nothing but delete the rows from 191 to 500. xlCellTypeBlanks does not consider a cell blank if it has a formula in it, even if the formula evaluates to blank. Wait... I just reread your original post and realized this is one of your issues you describe. Nevermind. I will address this when I post my final code.

Lastly, is there a step you take between Cleaner1() and Cleaner2()? Meaning, I would like to merge the two together into a single procedure, and so get rid of the unecessary insertion of rows blank rows only to have them deleted a moment later. Also, combining them will enable me to run loops to identify the rows to adjust, insert or delete without hard-coding the range addresses. Does this agree with your goals?

Hi Whizbang,
Thanks a zillion for your help. In the beginning, my aim was to copy data from a website to clipboard and then a macro will paste those 7 data and then go on to clean the data. Then these data will be accessed by the formula in sheet 1 to finally produce a report which will be printed.
However, being new to VBA, I could not handle the Microsoft form 2.0 library DataObject which is what is required. So I am manually pasting the first data which is the main block and contains 5 lines of past performances per racer. So I try to clean this bit up a bit with cleaner1 and then I will add one data set per racer hence the extra 6 data sets that make it all 7 sets of copied data. The problem is that these are of different length but not more than 15 hence my adding 15 lines to the initial data. Then, I use cleaner 2 to clean the data finally.
The aim is to have only 6 real races left behind for each racer. Hence my crude use of turning non real races like when the racer was a non-runner (NR), ran alone (solo) or with less than 5 others inchich case it is a T race into blanks for deletiing. Infact any race not having an A in the grade or having an NR entry anywhere in the row is to be removed. So I try to change this to blanks and then delete them along with other real blanks. Coming to the 6 in U, there are sometimes when an entrant has less that 6 races in its life time. In such case, I make up the number by repeating the very first race that has an "A" in the grade.
I hope I have clarified a few things. Please re-ask any questions I did not address well.
More importantly, thanks for your kindness.
Rgds.

Ok, now while running your code on the provided sample workbooks, I have come upt with a couple of questions/comments.

Why are you testing for the U column formula(s) to evaluate to six? Meaning, why copy data? It seems to me this is just to create filler rows. Why is this necessary? Why not just delete all blank rows?

This code:

Please Login or Register  to view this content.

doesn't seem to do anything. You have just filled the first 190 rows with a formula. So this line of code does nothing but delete the rows from 191 to 500. xlCellTypeBlanks does not consider a cell blank if it has a formula in it, even if the formula evaluates to blank. Wait... I just reread your original post and realized this is one of your issues you describe. Nevermind. I will address this when I post my final code.

Lastly, is there a step you take between Cleaner1() and Cleaner2()? Meaning, I would like to merge the two together into a single procedure, and so get rid of the unecessary insertion of rows blank rows only to have them deleted a moment later. Also, combining them will enable me to run loops to identify the rows to adjust, insert or delete without hard-coding the range addresses. Does this agree with your goals?[/QUOTE]

Originally Posted by snb

or ?

Please Login or Register  to view this content.

I get an error "Cannot use that command on overlapping sections". But, that is because the row number overlap some. If you do them one at a time, they work out.

Anyway, the code I am working on gets rid of that bit. It loops through each section and inserts the necessary rows at the same time it does the other operations.

Ok. Here is what I've cooked up in regards to Sheet1. I decided to make it simple and just use INDEX(). This allows you to fix the row and column numbers and ignore inserted and deleted rows and columns.

Please review the attached workbook and respond with any questions.

Originally Posted by snb

Please Login or Register  to view this content.

or ?

Please Login or Register  to view this content.

You know, I looked at my code several times, asking myself, "What would snb do?". I knew there was probably an easier way to do that without a loop, but I just couldn't see it.

Thanks again for suggesting ways to improve my code.

@snb
Yeah, I was tempted to go that route, but I wanted to show the original poster ways to improve his code to get the same result, but cleaner.

@bertlogdi1
If you want to see a procedure that would bypass the manual operation of copying and pasting from the webpage, please provide the URL for the website you are copying from. I don't have much experience in doing things with data from webpages, but I can at least get your started and other members of this forum can help finish the project.

My code, as it stands now, is:

Please Login or Register  to view this content.

I cannot thank you guys enough. The reason I am copying and pasting and then cleaning is because someone told me that there is no way to import javascript loaded data from websites. The website example: http://www.racingpost.com/greyhounds/card.sd

The data is not in HTML format but java delivered. As such my only option is to copy and paste it. One thing that I cannot understand is that when I paste the main data into excel the number for each racer tend to hover above the rest of the data and cover some part of the racer's name. I have tried to remove all hyperlinks from the excel page but these numbers and some others tend to still float above the underlying data and cannot be deleted.

If on the other hand I have been fed wrong information and it is indeed possible to parse these data from the website then I will be very happy if anyone can help out.

Once again thanks to both of you for your wonderful help.

Originally Posted by snb

In this case you are cleaning up a copied/pasted webpage.
If you use a webquery you can indicate which tables you want to be transferred. Probably it saves you a lot of 'cleaning' code.

You could try doing a pastespecial and paste is as text only. The numbers are actualy images that are embedded as part of your paste. You would need to right-click and "Cut" each one. There is probably a faster way to remove all embedded images, but nothing comes to mind. I don't usually have images in my workbooks, so I have limited experience in handling them on a large scale.

Hi Whizbang. First off, your code works like magic. The screen did not flash so things just happened!...magical! I thank you and snb very much.

Now I might be pushing my luck but there are things I would have loved to see looked into or added. Firstly, there are the other one block of data per racer to add to the main data. Usually, I would add those data after the first macro. Now with this one macro will it not be best to add the various data to the ends of each racer before running the macro. If that is the case will this not produce the wrong outcome given that the blocks to be added are different lengths?

Then I wiould have liked that the "m" in column C under "DIS" be removed so those boxes will for example read "380" rather than "380m". Also, it will be better if the bracket signs are removed from column D under "TRP" so one gets "6" rather than "[6]". Finally, in the "FIN" column which comes originally under column G, it will be nice to have only the numbers and no text so one gets "1" rather than "1st".

I hope I am not asking for too much. On the issue of thewebsite, I have been told that the data cannot be imported directly. How true this is I do not know. I have provided the link in my other post for your use to explore the chance of helping me further. One other thing though is that I have been told thet excel has a way of using Data.Object aspect of MS 2.0 library to handle pasting such that if implemented, I will only copy the 7 data to clipboard and the macro will paste them appropriately and clean the data. I hope someone can help in this wise.

All the same thanks to you guys for a splendid selfless help.
Rgds.

Originally Posted by Whizbang

My code, as it stands now, is:

Please Login or Register  to view this content.

Originally Posted by bertlogdi1

Firstly, there are the other one block of data per racer to add to the main data. Usually, I would add those data after the first macro. Now with this one macro will it not be best to add the various data to the ends of each racer before running the macro. If that is the case will this not produce the wrong outcome given that the blocks to be added are different lengths?

Please clarify this. What data is being added? Walk me through the process as if it were entirely manual.

Originally Posted by bertlogdi1

Then I wiould have liked that the "m" in column C under "DIS" be removed so those boxes will for example read "380" rather than "380m". Also, it will be better if the bracket signs are removed from column D under "TRP" so one gets "6" rather than "[6]". Finally, in the "FIN" column which comes originally under column G, it will be nice to have only the numbers and no text so one gets "1" rather than "1st".

This is all very doable. I will add them to my code and report back.

[QUOTE=bertlogdi1;2541753]On the issue of thewebsite, I have been told that the data cannot be imported directly. How true this is I do not know. I have provided the link in my other post for your use to explore the chance of helping me further. One other thing though is that I have been told thet excel has a way of using Data.Object aspect of MS 2.0 library to handle pasting such that if implemented, I will only copy the 7 data to clipboard and the macro will paste them appropriately and clean the data. I hope someone can help in this wise.QUOTE]
I will look into Webqueries and Data.Object aspect of MS 2.0 library. Like I said before, I do not have much experience (practically none) with working from webpages. I'll at least be able to post a few promising links, however.

Hi Whizbang,
Thanks again for your genorousity. This is how it happens manually:
1- I go to www.racingpost.com
2- I select the greyhound tab
3- I select the card section from the drop-down.
4- I click one of the races I am interested in
5- I scrol down to the bottom where the picks are and copy from there to the top stopping at the time of the event.
6- Then I copy the highlighted data.
7- Next, I will click on each individual entrant and from the pop-up, copy from the 6th till I get enough "A" races including the number of "A" races in the earlier 5 on the already copied data. Now this is where the variation occurs as some may have 5 "A"s already meaning they need only one race copied while others will need 6 races copied
8- Then off course there are those that do not have any extra A races to add nor do they have enough A races already. These ones will not get any data added instead the macro is expected to copy the first "A" race they have to make up the required 6.
9- For those that I have copied data for, they are those that will now require the addition of data.
I hope this is clear. If not please re-ask.
Regards.

[QUOTE=Whizbang;2542080]Please clarify this. What data is being added? Walk me through the process as if it were entirely manual.


This is all very doable. I will add them to my code and report back.

Originally Posted by bertlogdi1

On the issue of thewebsite, I have been told that the data cannot be imported directly. How true this is I do not know. I have provided the link in my other post for your use to explore the chance of helping me further. One other thing though is that I have been told thet excel has a way of using Data.Object aspect of MS 2.0 library to handle pasting such that if implemented, I will only copy the 7 data to clipboard and the macro will paste them appropriately and clean the data. I hope someone can help in this wise.QUOTE]
I will look into Webqueries and Data.Object aspect of MS 2.0 library. Like I said before, I do not have much experience (practically none) with working from webpages. I'll at least be able to post a few promising links, however.

Thanks.


This is all very doable. I will add them to my code and report back.[/QUOTE]

This is what you mean by a process you previously did between cleaner 1 and cleaner 2, correct

?

Yes indeed. Thanks for understanding.

This is handled by the macro already, correct?

Yes and thanks again.

What data is being added?

Same as first as above so no new addition. I will now go on and try it out and report back later. Rgds.

Hi Whizbang,
Once again thank you. I have tried the code and it runs fine (T and TT attached). However here are the remaining issues:
1- The code needs to be broken into two for now as I need to add the extra data copied for the entrants just after the point where the macro adds 15 extra rows.

2- The break point should be such that the addition of the rows occurs before the copying of any columns e.g. column U.

3- Is there a way to stop sheet one from changing the formula in the cells while the macro runs? That is can I have sheet one in an independent workbook which I only open and link to the worksheet that contains the macro such that the formulas will copy the right cells?

Thanks.