Run-time error ' 1004 ': Application-defined or object-defined error

I am attempting to write a program to sort the local bowls teams, randomize the names & print the players score cards. The sorting macro and printing programs are working, but the randomizing macro is tossing up the above error msg at the 1st list.

The sheet headings; A1: Surname,Christian, Posit (position), Surname, Christian, Posit, Surname, Christian, Posit, Rink

I have been trying to insert a column at A1; use =RAND & fill to the bottom of the first names list; copy & paste as values. Am using the macro generator (a bit long in the tooth for VBA).

I will then delete the added column & move to the next Surname list to repeat the process and once again for the players position in a team.

Please Login or Register  to view this content.

Hope this is enough info (waffle)
Thank you in advance
Muzza1

Please edit your post, the ending code tag is wrong. Its just [/code] not [/end code]

It's a recorded macro. There are hard coded offsets and I suspect that is the problem. I have no data to test but, if there are less than 15 rows, this will fail:

Please Login or Register  to view this content.

You would be better determining and storing the row number of the last data record and using that in your offset, adjusted where necessary.

Regards, TMS

Please Login or Register  to view this content.

Regards, TMS

So, the plan is to insert a column before column 5 (Surname (1)), and another before what would then be column 9 (Surname (2)) and generate random numbers in each one so that each set of names can be sorted? Although the numbers would be random, I'm guessing there's no guarantee that some, perhaps many, of the names will come out in the same sequence such that you have people playing against themselves.

OK, try this:

Please Login or Register  to view this content.

Regards, TMS

Can't look at it until later but your 'copy of screen' isn't very helpful. The code worked ok with your sample. Have you tried it with that? Your copy of screen Seems to be missing a column (4) (sheet)

The workbook you uploaded was a .xlsm file but there was no code in it. So, no, I couldn't click on any buttons. The code is designed assuming that it will insert columns at column H and column E.

I'll have another look later and see if I can adjust it.

Try:

Please Login or Register  to view this content.

Regards, TMS

I've posted later code than that. Why are you testing old versions of the code? We know they don't work if the sheet hasn't been processed.

See this code and the sample workbook attached. I've added Conditional Formatting to highlight when a name is duplicated.

Please Login or Register  to view this content.

Regards, TMS

Ah, OK, I misunderstood your process. I guess it would have helped if you didn't keep posting .xlsm files with no code in them. It's not possible to work through the process step by step. I'd assumed that you populated the columns D:E and F:H from columns A:C.

Comment out these two lines of code

Please Login or Register  to view this content.

Note the comments describing each section of code ...

so you get:

Please Login or Register  to view this content.

The problem being that the code does/did exactly what I expected/intended it to do every time ... just not quite what you expected.

Regards, TMS

And, if you want/need to sort all three blocks of data ...

Please Login or Register  to view this content.

Regards, TMS

The macro is now working. Can you give me the completed code as column A & B are not randomizing. I beleive that the first list of code that you sent needs to be edited into the front of you latest code?? This way we don't run the risk of me stuffing it up.
Muzza1

Posts may have crossed. See post #24.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.