Macro - Creating a better loop ?

[Exodus_NZ]

Hi guys

I was wondering if someone could look at my code and suggest a better way to loop through my tasks?

Please Login or Register  to view this content.

Problem 1A - with this is if the macro runs again (or more than once!), it actually deletes the important data in sheet 1 (Well duh obviously, since its just doing its job that ive programmed it to) - but obviously looking for a way to stop that from deleting things it shouldnt!

Once ROW 1 to ROW 49 deleted Box A1 has the word "Name" - so I guess I would need to do some search if A1 = 'Name' Halt?


Problem 1B - Also when the sheet is empty the Delimiter gives an error since there is nothing for it to do?


-----------------------------------------------

Problem 2: SOLVED by davsth (THANKS!)

This must be the worst loop in history! haha. So I pretty much have 18 Sheets (some are blank) - so I get errors on this macro - anyways All this does is copies the 21 rows from each sheet into a sheet called Stats.

Please Login or Register  to view this content.

-----------------------------------------------

Problem 3 - OK and last bit of advise I would like is on this bit of code ive made, This goes through everything in column A and deletes the junk numbers that were added to names/places - I also havent found a way to go through all my sheets using this wiping code - as it would be a loop inside a loop??

EG
List may be like this:

1Storm
1Richard
2Mike
4Dogs
15Richie
1C. Matthews
1P. Dixon
3C. Smith

Please Login or Register  to view this content.

Hope someone can help/point me in the right direction!

[davsth]

Problem 1, the code can be rewritten to run more quickly, but if it is not a problem the below can be added below the selection of sheet 1

Please Login or Register  to view this content.

For the second question
Sheets("11").Select
Range("B1:Y21").Select
Selection.Copy
Sheets("Stats").Select
Range("A270").Select
ActiveSheet.Paste


could be

Please Login or Register  to view this content.

It could be put into a loop to change the copy ranges, dependent on the sheet name being odd or even but im not sure its worth the effort

below will speed up the code
http://www.dummies.com/software/micr...p-your-macros/

[Exodus_NZ]

Problem 1, the code can be rewritten to run more quickly, but if it is not a problem the below can be added below the selection of sheet 1

Please Login or Register  to view this content.

For the second question
Sheets("11").Select
Range("B1:Y21").Select
Selection.Copy
Sheets("Stats").Select
Range("A270").Select
ActiveSheet.Paste


could be

Please Login or Register  to view this content.

It could be put into a loop to change the copy ranges, dependent on the sheet name being odd or even but im not sure its worth the effort

below will speed up the code
http://www.dummies.com/software/micr...p-your-macros/

Thank you , that has solved my problem 2 perfectly! I will research the link you sent for more answer to my Problem 1 and 3

Regards

[mjr veverka]

Ad 3. Try this way:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

Don't use 'Item' as variable, it's a reserved word in vba.
If 'For Each ... Next' loop, then only objects or variants.

Ad 1, 2. ???

Please Login or Register  to view this content.

[Exodus_NZ]

Ad 3. Try this way:

Please Login or Register  to view this content.

Hi

Thank you so much, I am getting an error "Type mismatch"

and when I click debug it says its because of this line: If .Range("A1").CurrentRegion.Rows > 0 Then

[mjr veverka]

...
Change to:

Please Login or Register  to view this content.

... '.Rows' itself is an object, not its number, only '.Rows.Count' gives a number.

[Exodus_NZ]

Hi guys

Currently using this code and it works great most of the time, but sometimes there is a "zero" (0) left before the name

ie

40Jam. Tuttle
40O. Tu'ungafasi
40A. van der Merwe

Becomes


0Jam. Tuttle
0O. Tu'ungafasi
0A. van der Merwe

Please Login or Register  to view this content.

[davsth]

Not seeing the workbook and not being the best at VBAs, I wonder if the array you are using to replace stuff with contains 0. it doesn't at the beginning of this post it is 1 to 18. I thing you need to add to your array

what if you change

Please Login or Register  to view this content.

[mjr veverka]

Currently using this code ... but sometimes there is a "zero" (0) left before the name
40Jam. Tuttle
...
Becomes
0Jam. Tuttle

That's right, because before the number "0" e.g. in the number "40" you have a different number, here just "4", and 'Replace' works with 'LookAt:=xlPart' argument.
To remove numbers, you have to change the approach to this process, to eliminate them from the string.
E.g.:
a) Each character in a string, starting from position #1, must be checked whether it is a number or not, until we hits the letter character
or
b) maybe we should use some search mask
or
c) something else ...