"sumesh56" wrote:
sumesh56;1616760 Wrote:
i tried the macro in another worksheet. it gives me error
message. it says"run-time error'1004' method 'range' of
object'global failed. [....] the worksheet is attached for
your inspection.

I had not seen this message. Something very odd is going with my
newsreader. And I am unable to log into excelbanter.com at the moment to
respond. But I can read your postings there.

The file attached to the excelbanter.com posting was saved as "xlsx" instead
of "xlsm", so the macro was removed. Consequently, I cannot see what might
be wrong with it.

Looking at the Excel part of "test jasua 240314.xlsx", I see the following
potential problems:


1. Column F of the Distribution worksheet is missing the formulas of the
form (F4 for example):

=IF(COUNT(D4,E4)=2,E4-D4+1,"")

Consequently, the SUM formula in column G always returns zero.

Would you like me to add code to the macro so the formulas in column F are
created automatically?


2. The "CUSTOMIZE" part of the macro must be changed as follows:

Set distribWS = Sheet2 ' template
Set seatingWS = Sheet1

This is because the Seating worksheet is the Sheet1 object and the
Distribution worksheet is the Sheet2 object in the "test jasua 240314" file.

That is opposite of the original file "test seating plan 210314".

The difference itself is not a problem. It is simply something that you
must pay attention to and modify the "CUSTOMIZE" part of the macro
accordingly, as I explained in one of my previous responses.


"sumesh56" wrote:
your macro worked in the worksheet which was downloaded from your
suggestion. but it is not working in another worksheet in which i have
changed only the contents and the number of tables.is it possible to
edit your macro to suit any number(variable) of table of contents?

It should work with any number of tables already. There is no dependency on
the number of tables, as I explained before. Only the dependencies that I
explained in my previous message.

(In fact, it does work with the "test jasua 240314" file, after I import the
macro and customize it accordingly, as noted above.

Please provide the file that does not work.

If you are talking about "test jasua 240314", please recreate it (add the
macro again), and be sure to save it as "xlsm".

(Be sure to delete or change any private information like specific
identities.)

Did you make the necessary changes, if any, to the part of the macro marked
"CUSTOMIZE"?

In particular:

1. Do you need to change seatingTitle, perhaps from "exam room" to just
"room"?

(Not necessary in the "test jasua 240314" file.)

2. Do you need to change distribWS and seatingWS?

(Yes, this necessary in the "test jasua 240314" file, as noted above.

Download the JPG at https://app.box.com/s/4qnrxcw6pthp6g52qtug to see what
might need to be changed in the "CUSTOMIZE" part of the macro, and how.