A worksheet realted to the conduct of exams.
I have a sheet named SEATING PLAN which says who to sit in which exam room.
I have another sheet named DISTRIBUTION which displays each class division to diffeent exam roooms.
What i am doing is first prepare the seating plan then copy paste the concerned lines(col C:E) to the distribution sheet. This is a tedious job.
Is it possible to ask excel to do the job for me? I will prepare the seating plan and excel has to fill the distribution sheet columns C:E. thanks.pls see the sample excel file attached.

Hi,

Am Fri, 21 Mar 2014 01:33:19 +0000 schrieb sumesh56:

I have a sheet named SEATING PLAN which says who to sit in which exam
room.
I have another sheet named DISTRIBUTION which displays each class
division to diffeent exam roooms.

I don't see any matching data in your two sheets. Not even the class and
the classes do match.
Can you explain more or can you put the expected values into sheet
"DISTIBUTION" and upload that file?


Regards
Claus B.
--
Vista Ultimate SP2 / Windows7 SP1
Office 2007 Ultimate SP3 / 2010 Prodessional

thanks a lot for the interest shown on my subject. the end result is shown in the sheet named:distribution" i will try to elaborate.

C4-E4 of the sheet named"seating plan"matches with C8-E8 of the sheet named distribution.likewise C12-E12 matches with C9-E9 and C22-E22 with C10-E10 and C48-E48 with C11-E11.the seating plan says that in XII-A Examroom students belonging to classes XI-A,X-A,IX-B AND VIII-A are accomodated.thus total of 40 students are accomodated in XII-A.There are 36 students in class XI-A ROLL NUMBERS 601-636.they are accomodated in four examrooms viz XII-A,XII-B,XI-A AND X-B.
We have assigned rollnumbers like this--
601 to 650 will be in class XI-A and 651 to 699 will be in class XI-B. This procedure will continue for all classes. so is it possible to connect something with the rollnumbers? when you sort rollnumbers 601 to 650 all the students who belong to class XI-A will come together.601=class XI,500=X,400=IX,600=VIII,200=CLASS VII AND 100= CLASS VI. the result will be the sheet named "distribution " . thanks

"sumesh56" wrote:
What i am doing is first prepare the seating plan
then copy paste the concerned lines(col C:E) to the
distribution sheet. This is a tedious job.
Is it possible to ask excel to do the job for me?
[....]
Download: http://www.excelbanter.com/attachmen...tachmentid=953

Yes, but only if you ask Excel nicely. ;-)

Seriously, you need to explain the algorithm in some detail, even to us.
That is, what steps you take manually. It might go something like this (I
am guessing):

1. For each "class" in Distribution column B (B8, B16, B25, etc)
2. For each matching (distribution) "classes" in Seating column C
3. copy corresponding Seating "class" (column B) into next
Distribution column C
4. copy corresponding Seating "from" and "to" (columns D:E) into next
Distribution!D:E

For example, for Distribution!B8, we find matching C4, C12, C22 and C48 in
Seating. For matching Seating!C4, for example, we copy Seating!B4 to
Distribution!C8, and we copy Seating!D4:E4 to Distribution!D8:E8.

This presumes there is always sufficient space in each Distribution "class".
It is curious (odd) that some Distribution groups ("class") have 4 lines,
and some have 5 lines.

For one VBA implementation, download "test seating plan 210314.xlsm" from
https://app.box.com/s/f7yddu2xcrki8wx5mr8t. For posterity, I include the
macro text below.

The VBA macro corrects some copy-and-paste/edit errors that you made
originally, to wit:

1. In Distribution!C44, the seating class name is VII-A instead of IX-A.
See Seating!B96.
2. In Distribution!E107:F107, the numbers are 175 and 10 instead of 185 and
20. See Seating!E91:F91.
3. Additional Distribution!C108:E108 from Seating!B42:E42.

Also note that the order is different the Distribution tables starting at
B43 and B75. The order of VBA macro results is the order of appearance in
Seating. Apparently, you sorted the results. Is that necessary?

If yes, sorting can be added to the macro.



Design note: The formula in each group in Distribution column F should be
of the form:

=IF(COUNT(D8,E8)=2,E8-D8+1,"")

This makes it easier to copy and clear the Distribution data initially.

You could do similarly in each group in Seating column F.

"sumesh56" wrote:
C4-E4 of the sheet named"seating plan"matches with C8-E8 of the sheet
named distribution.likewise C12-E12 matches with C9-E9 and C22-E22 with
C10-E10 and C48-E48 with C11-E11.the seating plan says that in XII-A
Examroom students belonging to classes XI-A,X-A,IX-B AND VIII-A are
accomodated.thus total of 40 students are accomodated in XII-A.There are
36 students in class XI-A ROLL NUMBERS 601-636.they are accomodated in
four examrooms viz XII-A,XII-B,XI-A AND X-B.
We have assigned rollnumbers like this--
601 to 650 will be in class XI-A and 651 to 699 will be in class XI-B.
This procedure will continue for all classes. so is it possible to
connect something with the rollnumbers? when you sort rollnumbers 601 to
650 all the students who belong to class XI-A will come
together.601=class XI,500=X,400=IX,600=VIII,200=CLASS VII AND 100=
CLASS VI. the result will be the sheet named "distribution " . thanks

Our posting crossed in the ether(net). I had inferred your algorithm. I
have not compared my inference to your description above. Please let me
know if I missed anything.

PS.... I wrote:
"joeu2004" wrote in message
...
For one VBA implementation, download "test seating plan 210314.xlsm" from
https://app.box.com/s/f7yddu2xcrki8wx5mr8t. For posterity, I include the
macro text below.

Some after-thoughts....

I might note that the implementation is not as efficient as it might be.
But it is sufficiently fast for the example. And I think the design is
easier to understand than a more efficient algorithm.

Also, I forgot to "include the macro text below" for posterity. Here it is.

-----

Option Explicit

Sub makeDistrib()
Dim vSeating As Variant, vDistrib As Variant
Dim nSeating As Long, nDistrib As Long
Dim i As Long, j As Long, nRows As Long
Dim r As Range, nr As Long, nc As Long
Dim distribWS As Worksheet, seatingWS As Worksheet
Dim newDistrib As String, className As String

'**** CUSTOMIZE ****
Set distribWS = Sheet1 ' template
Set seatingWS = Sheet2

' copy Distribution template
distribWS.Copy Befo=Sheets(1)
newDistrib = "NEW " & distribWS.Name
On Error Resume Next
ActiveSheet.Name = newDistrib
If Err < 0 Then
' delete worksheet with duplicate name
Application.DisplayAlerts = False
Sheets(newDistrib).Delete
Application.DisplayAlerts = True
ActiveSheet.Name = newDistrib
End If
On Error GoTo 0

nRows = Rows.Count

' copy in seating data
With seatingWS
nSeating = .Cells(nRows, "e").End(xlUp).Row
vSeating = .Range("b1", .Cells(nSeating, "e"))
End With
' fill in and trim seating class names.
' also trim distribution class names to
' facilitate match later
i = 1
Do
If LCase(vSeating(i, 1)) = "class" Then
i = i + 1
className = Trim(vSeating(i, 1))
Do While vSeating(i, 2) < ""
vSeating(i, 1) = className
vSeating(i, 2) = Trim(vSeating(i, 2))
i = i + 1
If i nSeating Then Exit Do
Loop
End If
i = i + 1
Loop Until i = nSeating

' copy in distribution tables
nDistrib = Cells(nRows, "b").End(xlUp).Row
vDistrib = Range("b1", Cells(nDistrib, "b"))

' for each distribution table, copy seating data
i = 1
Do
If LCase(vDistrib(i, 1)) = "class" Then
' clear Distribution template
i = i + 1
Set r = Cells(i, "b").MergeArea
nr = r.Rows.Count
Range("c" & i & ":e" & i + nr - 1).ClearContents
' copy seating data
className = Trim(vDistrib(i, 1))
For j = 1 To nSeating
If vSeating(j, 2) = className Then
' copy seating data
Range("c" & i) = vSeating(j, 1)
Range("d" & i) = vSeating(j, 3)
Range("e" & i) = vSeating(j, 4)
i = i + 1
End If
Next j
End If
i = i + 1
Loop Until i = nDistrib
MsgBox "done"
End Sub

"sumesh56" wrote:
the seating plan says that in XII-A Examroom students belonging
to classes XI-A,X-A,IX-B AND VIII-A are accomodated.
thus total of 40 students are accomodated in XII-A.
There are 36 students in class XI-A ROLL NUMBERS 601-636.
they are accomodated in four examrooms viz XII-A,XII-B,XI-A AND X-B.
We have assigned rollnumbers like this-- 601 to 650 will be in
class XI-A and 651 to 699 will be in class XI-B.
[....] is it possible to connect something with the rollnumbers?

Yes. I did not have the benefit of this design information before.

(Re)Download download "test seating plan 210314.xlsm" from
https://app.box.com/s/f7yddu2xcrki8wx5mr8t. I have updated the marco to
take advantage of the meaning of the roll numbers.

Also, I took the liberty of changing the titles in column B of the Seating
worksheet to "EXAM ROOM", since that seems to be your intent.

(I never did understand why column B was titled "CLASS" in both worksheets.)

The VBA implementation could still be optimized to avoid the O(n*(n+1)/2)
search time. And it is always prudent to toggle Application.ScreenUpdating,
Calculation mode and EnableEvents.

Again, I do not bother because the macro is fast enough for the example, and
it is easier to understand as written for now.

-----

Updated macro....

Option Explicit

Sub makeDistrib()
Dim vDistrib As Variant, vData As Variant
Dim nSeating As Long, nDistrib As Long, nData As Long
Dim i As Long, j As Long, nRows As Long
Dim r As Range, nr As Long
Dim distribWS As Worksheet, seatingWS As Worksheet
Dim newDistrib As String, className As String, roomName As String

'**** CUSTOMIZE ****
Set distribWS = Sheet1 ' template
Set seatingWS = Sheet2
newDistrib = "NEW " & distribWS.Name

nRows = Rows.Count

' copy in seating data.
' trim and copy seating room names.
' also trim distribution class names to facilitate match later.
' NOTE: i=i+1 assumes at least one line of separation between
' seating tables
With seatingWS
nData = .Cells(nRows, "e").End(xlUp).Row
vData = .Range("b1", .Cells(nData, "e"))
End With
ReDim vSeating(1 To nData, 1 To 4) As Variant
i = 1: nSeating = 0
Do
If LCase(vData(i, 1)) = "exam room" Then
i = i + 1
roomName = Trim(vData(i, 1))
Do While vData(i, 2) < ""
nSeating = nSeating + 1
vSeating(nSeating, 1) = roomName
vSeating(nSeating, 2) = Trim(vData(i, 2))
vSeating(nSeating, 3) = vData(i, 3)
vSeating(nSeating, 4) = vData(i, 4)
i = i + 1
If i nData Then Exit Do
Loop
End If
i = i + 1
Loop Until i = nData

' sort by roll number
Sheets.Add befo=Sheets(1)
Set r = Range("a1:d" & nSeating)
r = vSeating
r.Sort Key1:=r.Cells(1, 3), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
vSeating = r
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True

' copy Distribution template
distribWS.Copy befo=Sheets(1)
On Error Resume Next
ActiveSheet.Name = newDistrib
If Err < 0 Then
' delete worksheet with duplicate name
Application.DisplayAlerts = False
Sheets(newDistrib).Delete
Application.DisplayAlerts = True
ActiveSheet.Name = newDistrib
End If
On Error GoTo 0

' copy in distribution tables
nDistrib = Cells(nRows, "b").End(xlUp).Row
vDistrib = Range("b1", Cells(nDistrib, "b"))

' for each distribution table, copy seating data.
' NOTE: i=i+1 assumes at least one line of separation between
' distribution tables
i = 1
Do
If LCase(vDistrib(i, 1)) = "class" Then
' clear Distribution template
i = i + 1
Set r = Cells(i, "b").MergeArea
nr = r.Rows.Count
Range("c" & i & ":e" & i + nr - 1).ClearContents

' copy seating data
className = Trim(vDistrib(i, 1))
For j = 1 To nSeating
If vSeating(j, 2) = className Then Exit For
Next
If j <= nSeating Then
Do
Range("c" & i) = vSeating(j, 1)
Range("d" & i) = vSeating(j, 3)
Range("e" & i) = vSeating(j, 4)
i = i + 1
j = j + 1
If j nSeating Then Exit Do
Loop Until vSeating(j, 2) < className
End If
End If
i = i + 1
Loop Until i = nDistrib
MsgBox "done"
End Sub

I wrote:
"sumesh56" wrote:
is it possible to connect something with the rollnumbers?

Yes. I did not have the benefit of this design information before.

(Re)Download download "test seating plan 210314.xlsm" from
https://app.box.com/s/f7yddu2xcrki8wx5mr8t. I have updated the
marco to take advantage of the meaning of the roll numbers.

Interestingly, with the example data, the original version that does not
sort roll numbers is about 26% faster as written, and 12% faster when we
toggle Application.ScreenUpdating.

I emphasize "with the example data" because I suspect the implementation
that sorts would be faster with some larger data. But perhaps it must be
__much__ larger data. I did not try to determine the break-even point.

Neverthelss, I would stick with the original non-sorting implementation
because it might be simpler to understand.

The file "test seating plan 210314.xlsm" at
https://app.box.com/s/f7yddu2xcrki8wx5mr8t now contains both
implementations.


I wrote:
And it is always prudent to toggle Application.ScreenUpdating, Calculation
mode and EnableEvents.

Application.ScreenUpdating: yes. An improvement of 57% to 64%.

Application.Calculation: no, surprisingly. A degradation of 19% to 26%
when we toggle ScreenUpdating.

But I'm splitting hairs. It is a maximum difference of only 30 milliseconds
(on my computer; YMMV) for the example data when we toggle ScreenUpdating.

(I use the more-accurate performance counter to measure time, not Timer,
which can be off by as much as 15.625 milliseconds, not suitable of
measuring differences this small.)

thanks for the reply .a suggested i have changed the titles of column B as ROOM sheet seatingplan and CLASS in distribution.for better understanding of the situation i need to further explain.
kindly refer to attached file renewed seating plan220314 edited.there are three sheets on that workbook. sheet named seating plan -sheet named desired output in which i have shown the desired output. the third one sheet named distribution is for displaying the result.
1)only to copy the contents from B:E
2)both the sheets are identical except the column headers.
3)lets try a few lines in the output sheet.
4)i want something like this---

a)IF seatingplan!B4:E7,"=601"and "<=650",then copy
and paste the contents from seatingplan!B4=distribution!C4 and seatingplan!D4:E7=distribution!D4:E4.

b)IF seatingplan!B12:E15,"=601"and "<=650",then copy
and paste the contents from seatingplan!B12=distribution!C5 and seatingplan!D12:E15=distribution!D5:E5.

c)IF seatingplan!B22:E25,"=601"and "<=650",then copy
and paste the contents from seatingplan!B22=distribution!C6 and seatingplan!D22:E25=distribution!D6:E6.

d)IF seatingplan!B33:E36,"=601"and "<=650",then copy
and paste the contents from seatingplan!B33=distribution!C7 and seatingplan!D33:E36=distribution!D7:E7.

5)If we succeed in putting the If formulas correctly or any other suitable syntax, the distribution sheet will be filled in up by excel.

"sumesh56" wrote:
4)i want something like this---

a)IF seatingplan!B4:E7,"=601"and "<=650",then copy
and paste the contents from seatingplan!B4=distribution!C4 and
seatingplan!D4:E7=distribution!D4:E4.

b)IF seatingplan!B12:E15,"=601"and "<=650",then copy
and paste the contents from seatingplan!B12=distribution!C5 and
seatingplan!D12:E15=distribution!D5:E5.

c)IF seatingplan!B22:E25,"=601"and "<=650",then copy
and paste the contents from seatingplan!B22=distribution!C6 and
seatingplan!D22:E25=distribution!D6:E6.

d)IF seatingplan!B33:E36,"=601"and "<=650",then copy
and paste the contents from seatingplan!B33=distribution!C7 and
seatingplan!D33:E36=distribution!D7:E7.

Sorry, but I cannot help you with that. I think it is a flawed approach,
lacking in generality.

Hopefully someone else will jump in.

thanks a lot for the help done so far. I have downloaded

your macro enabled file.there are three sheets.It is not

clear for me where is the result displayed. I am not able

to understand what exactly has to do.I have not the

experience of using macros. shall I ask?
1)I want to try your code.Please instruct me in detail

what to do.
2)In my original ws,the rows where the data started were

not identical in both the sheets. In seating plan it

started from row 2 whereas in distribution it started from

row6. can i delete the upper blank rows in sheet

distribution without affecting your code?
3)To work your code,what are the things I must have?If I

have only the sheet seatingplan and if I apply your code

to it will I get the distribution sheet as an effect of

the macro? Or should I have the distribution! with blank

cells from C:E to get the results? thanks.

"sumesh56" wrote:
I have downloaded your macro enabled file. there are three sheets.
It is not clear for me where is the result displayed. I am not able
to understand what exactly has to do. I have not the experience of
using macros. shall I ask?

Your questions are very appropriate. I apologize for not explaining things
more completely.

My uploaded file is based on your original file that has a Seating Plan and
a Distribution worksheet. The third worksheet titled "NEW Distribution ..."
is the result of executing the macro.

In the final version of my uploaded file, I have __two__ macros: one that
sorts the Seating Plan data by roll number (makeDistrib2), and one that does
not (makeDistrib1).

Use one or the other; your choice. You can delete the macro you do not use,
if you wish. I provide some VBA usage instructions at the end below.

-----

"sumesh56" wrote:
2)In my original ws, the rows where the data started were
not identical in both the sheets. In seating plan it
started from row 2 whereas in distribution it started from
row6. can i delete the upper blank rows in sheet
distribution without affecting your code?

Yes. I tried to make things as flexible as possible, relying on as little
as possible. Hopefully the following explanation will make that clear.

-----

"sumesh56" wrote:
1)I want to try your code. Please instruct me in detail
what to do.
[....]
3)To work your code,what are the things I must have? If I
have only the sheet seatingplan and if I apply your code
to it will I get the distribution sheet as an effect of
the macro? Or should I have the distribution! with blank
cells from C:E to get the results?

I assume you provide both the Seating Plan worksheet with all the details
and a Distribution worksheet with tables that can be used as template.

That is what you described in your original posting, to wit: "then copy
paste the concerned lines (col C:E) to the distribution sheet".

(The macro deletes any pre-existing data that might be in columns C:E in the
Distribution tables.)

You can add and remove tables in the Seating Plan and Distribution
worksheets, as long as you adhere to the following expections.

1. The Distribution worksheet must have at least the same tables (exam
rooms) that are referenced in column C of the Seating Plan.

It can have more tables for exam rooms that are not referenced in the
Seating Plan.

2. Each Distribution table (exam room) must have at least as many rows as
there are references to that table in the Seating Plan.

The Distribution tables can have more rows than are not needed. For
example, exam room XI-B has 4 rows, but only 3 rows are used.

3. The title in column B in each Seating Plan table must be "EXAM ROOM". It
does not matter if it is uppercase, lowercase or mixed case.

4. The title in column B in each Distribution table must be "CLASS". Again,
it does matter if it is uppercase, lowercase or mixed case.

NOTE: In a later posting, you say the Seating Plan table title in column B
is now "ROOM", not "EXAM ROOM". Either change the title to "EXAM ROOM" (you
can use Find And Replace), or download an updated version of my file and
change the values of distribTitle and seatingTitle.

5. There must be at least one empty row between tables in both the Seating
Plan and Distribution worksheets.

6. The merged cells in column B in each Distribution table must include
__all__ rows in the table, including unused rows.

You do that already in column B. But I notice you did not always do that in
column G ("TOTAL NOS"). That's okay because the macros do not depend on
column G. But I wanted to be sure you do not make that "mistake" in column
B.

PS.... I wrote:
The following is some brief VBA instructions.

I forgot to mention that my file contains 3 VBA modules. Module2 contains
makeDistrib2 (sorts roll numbers). Module1 contains makeDistrib1 (does not
sort). Module3 contains timer routines.

5. In the Project Explorer (ctrl+R), double-click on Module1 or Module2 to
see the code.

6. To delete one or the other, if you wish, right-click on Module1 or
Module2 in the Project Explorer, and click on Remove Module.

thanks a lot for the help done so far. I have downloaded your macro enabled file.there are three sheets.It is not clear for me where is the result displayed. I am not able to understand what exactly has to do.I have not the experience of using macros. shall I ask?
1)I want to try your code.Please instruct me in detail what to do.
2)In my original ws,the rows where the data started were not identical in both the sheets. In seating plan it started from row 2 whereas in distribution it started from row6. can i delete the upper blank rows in sheet distribution without affecting your code?
3)To work your code,what are the things I must have?If I have only the sheet seatingplan and if I apply your code to it will I get the distribution sheet as an effect of the macro? Or should I have the distribution! with blank cells from C:E to get the results? thanks.

i copy pasted your code to a new sheet at the end of the sheets already in my file named testseatingplan210314.and when i run the macro it gives me error message, it says "can't excecute code in break mode".in the code the word befo is highlighted.(loop until i=nData
sort by roll number
sheets.Add befo =sheets(1)

"sumesh56" wrote:
i copy pasted your code to a new sheet at the end of the sheets already
in my file named testseatingplan210314.and when i run the macro it gives
me error message, it says "can't excecute code in break mode".in the
code the word befo is highlighted.(loop until i=nData
sort by roll number
sheets.Add befo =sheets(1)

First, Download my file "test seating plan 210314.xlsm" again; same URL:
https://app.box.com/s/f7yddu2xcrki8wx5mr8t.

I simplifed things to make it easier to copy-and-paste the macro. One
macro: the one that sorts; I think you'd prefer that. No dependency on
timer macros.

I also corrected the misnamed variables distribTitle and seatingTitle. The
code worked before as written. But it is like having a variable "two" whose
value is 3. Misleading! My bad!

Also, the updated file shows the workbook in its initial state: just two
worksheets, and the Distribution worksheet has no data in columns C:E.

You can run the macro and see how it changes the workbook, namely: it
creates a new worksheet called "NEW Distribution ...", and it leaves the
original Distribution worksheet unmodified.

-----

That has anything to do with your immediate problem.

I don't know why you were in break mode. Did you stop the execution of a
macro using a Stop statement, a breakpoint (F9) or single-stepping (F8)? Or
did you get a run-time error and click on Debug instead of End?

To avoid the problem, click on Run, then Reset before pasting or importing
the macro code into a workbook.

Also, the simplified macro noted above will probably avoid a run-time error
when you copy-and-paste or import the code into other workbooks.

-----

You do not need to copy the macro into each sheet module. In fact, the
implementation probably does not work in a sheet module.

It is sufficient (and probably necessary) to have one copy of the macro in a
normal module.

To copy the macro into a workbook:

1. In VBA, click on Insert, then Module.
2. Copy the code text, and paste into the new module.

Alternatively, save ("export") the code module one time by doing the
following:

1. In VBA, with the desired module selected, click on File, then Export.
2. Navigate to a folder of your own, usually under My Documents.
3. Change Module2.bas in the File Name field to something appropriate, e.g.
makeDistrib.
4. Click on Save.

Or you can download makeDistrib.bas from
https://app.box.com/s/buzzwbxemee7xgnr4dgz.

Then, to import the code module into a workbook:

1. In VBA, click on File, Import.
2. Navigate to the folder where you saved the module.
3. Find and double-click on the ".bas" file that you saved.

-----

Hope that helps. Sorry this is so difficult. But I believe a VBA solution
is the best, if not the only, way to do what you want.

thanks a lot for the reply and guidance. let me check and report back. have a nice day.

thanks a lot for the pain to produce the macro for me.
As you suggested I exported your module and saved it as makedistribution.bas in another folder. I wanted to import the module into a worksheet. As you suggested VBA-insert-module-navigated to the folder where i have saved the module makedistribution.bas.Double clicked on it. i checked on the module in the worksheet.I couldnot see the module named makedistribution there. instead i saw simple name module. no code was there. you said copy the code text and paste it ino the module. I can not understand the point that,if the code is to be pasted to the module what is the use of importing the module? hope you will explain.
btw, your code worked on the ws downloaded from your direction. but i tried the macro in another worksheet. it gives me error message. it says"run-time error'1004'
method 'range' of object'global failed.
I have taken seatingplan! B2:G61 and distrib!B2:G72.the worksheet is attached for your inspection. thanks.

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?

"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.

thanks a lot for the reply. i am ashamed to see that lot of trouble i am giving to you. but sorry. as i told you earlier i am not an expert in using macro. indeep this is the first time i am using one. only recently i am studying about macro. out of curiosity only i am asking silly (it may seem to others)Qtns.let me check and report back.have a nice day.

thank you very much. it works. i have checked it in a few files and it works without any problem. thanks once again for the pain you have taken to produce the macro and for patiently answering my queries.
i have some more queries.from the postings i am quoting these lines.




It is sufficient (and probably necessary) to have one copy of the macro in a
normal module.
================================
what do you mean by normal module. do you mean that a module which is saved as a .bas file somewhere outside he worksheet?
=================================================


I don't know why you were in break mode. Did you stop the execution of a
macro using a Stop statement, a breakpoint (F9) or single-stepping (F8)? Or
did you get a run-time error and click on Debug instead of End?

To avoid the problem, click on Run, then Reset before pasting or importing
the macro code into a workbook.
-===================================
i need explanation to this how can i reset before pasting?
-----------------------------------------------------------------------


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?

kindly do that for me.

===================================
Please provide the file that does not work.
====================================
I wish that never happen. hope that your macro is so strong.

"sumesh56" wrote:
sumesh56;1616779 Wrote:
as i told you earlier i am not an expert in using macro.
[....] i am asking silly (it may seem to others)Qtns.

Yes, I understand that you are new to VBA. Mistakes happen, even to
experienced VBA programmers. There are no dumb (silly) questions; only dumb
responses that call a question "silly".

That said, my only frustration is that you do not answer questions, and you
do not provide information requested. Something to keep in mind for the
future.


"sumesh56" wrote:
i have checked it in a few files and it works without any
problem.

Great! Good to hear.


"sumesh56" wrote:
[joeu2004 wrote:]
It is sufficient (and probably necessary) to have one copy
of the macro in a normal module.
================================
[sumesh56 wrote:]
what do you mean by normal module. do you mean that a module
which is saved as a .bas file somewhere outside he worksheet?
=================================================

No. If you look at the Project Explorer in VBA (press ctrl+R, if
necessary), you might see something like:

VBAProject (test seating plan...)
Microsoft Excel Objects
Sheet1 (Distribution...) <-- a sheet module
Sheet2 (Seating...)
ThisWorkbook
Modules
Module1 <-- normal module

You might not see a normal module until, in VBA, you click on Insert, then
Module.

In Excel, if you right-click on the worksheet tab, then click on View Code,
it takes you to the sheet module (e.g. object Sheet1).

I inferred that you had copied the macro code into a sheet module instead of
a normal module because you wrote previously: "i copy pasted your code to a
new sheet".


"sumesh56" wrote:
[joeu2004 wrote:]
I don't know why you were in break mode.
[....]
To avoid the problem, click on Run, then Reset before pasting
or importing the macro code into a workbook.
-===================================
[sumesh56 wrote:]
i need explanation to this how can i reset before pasting?
----------------------------------------------------------

As I said: in VBA, click on Run, then Reset.


"sumesh56" wrote:
[joeu2004 wrote:]
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,"")
[....]
===================================
[joeu2004 wrote:]
Please provide the file that does not work.
====================================
[sumesh56 wrote:]
I wish that never happen. hope that your macro is so strong.

I do not understand the English here.

My guess: "I wish that never happen" means "sorry about that omission; my
mistake; unintended"? Okay.

I do not understand "hope your macro is so strong" at all.

I had offered to modify the macro so that is creates the formula in column
F. Are you saying: "yes, please make that change"?

"That said, my only frustration is that you do not answer questions, and you
do not provide information requested. Something to keep in mind for the
future."
================================================== =====
"I inferred that you had copied the macro code into a sheet module instead of
a normal module because you wrote previously: "i copy pasted your code to a
new sheet".
================================================== =====
what you have inferred was correct. i used to manage with the sheet tab for inserting the code.
----------------------------------------------------------------------

"As I said: in VBA, click on Run, then Reset."
================================================== ======
i do not understand how to reset. when i clicked on the macros button in the code group i get run btn but not reset btn.there is nothing like reset in the options also.that is why i asked you again.
----------------------------------------------------------------------
""I wish that never happen. hope that your macro is so strong.""
================================================== ======
sorry for the poor english. what i intended was,since your macro seems perfect,there will be chance for me to quote a file in which your macro is not working properly.
------------------------------------------------------------------------


"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?

kindly do that for me."
================================================== =======
here i requested you to kindly include the formula in the code.
----------------------------------------------------------------------
"i have checked it in a few files and it works without any
problem."
==================================================
with this comment i wanted to convey that after incorporating your directions as to edit the customize part, the code is working perfect.that is why i did not answer the qtns. sorry for that.

-----------------------------------------------------------------
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?
================================================== ==
i do not want to change the exam room title to anything else.it is ok.
as you have suggested ,
yes it was necessary for the code to perform . so i changed the customize part as suggested.
----------------------------------------------------------------------
everytime i search for the necessary lines in the postings to quote the link with the excelbanter is going. so i am doing it in a text file and copy paste the comments. thats why i am not able to show the quoted part in my feedback.please suggest how can i insert quote tag wherever i want?
------------------------------------------
in your feedback some lines are in different color say green. how do you accomplish this?
================================================== ===






Now that a herculian task has been accomplished through creating makedistib macro i would like to request you to kindly create one more macro named createtemplate so that when i click on the seatingplan! and run the createtemplate macro i can get the distribution template. as you know there is no serious differences except the col headers. other columns are the same.

pls read as
sorry for the poor english. what i intended was,since your macro seems perfect,there will be no chance for me to quote a file in which your macro is not working properly.

"sumesh56" wrote:
[joeu2004 wrote:]
Would you like me to add code to the macro so the
formulas in column F are created automatically?

kindly do that for me.

Download makeDistrib.bas from https://app.box.com/s/buzzwbxemee7xgnr4dgz.

In addition to creating the formulas in column F, the new version also
creates the SUM formula in the cell of column G for each table.

You can import the file into any workbook by doing the following in VBA (not
Excel):

1. Click on File, then Import File.
2. Navigate to the folder into which you downloaded the file
3. Enter makeDistrib.bas into the File Name field.
4. Click on Open.


"sumesh56" wrote:
kindly create one more macro named createtemplate so
that when i click on the seatingplan! and run the
createtemplate macro i can get the distribution template.

I'm sorry, but I do not have time to do that.


"sumesh56" wrote:
[joeu2004 wrote:]
As I said: in VBA, click on Run, then Reset.

i do not understand how to reset. when i clicked on the
macros button in the code group i get run btn but not
reset btn. there is nothing like reset in the options also.

I think you are describing features that you see in Excel.

I am talking about features that you would see __in_VBA__.

Start by opening the VBA window. There are many ways to accomplish that,
starting in Excel.

1. Press alt+F11.

or

2. Click on Developer, then Visual Basic.

or

3. Right-click on a worksheet tab, then click on View Code.

In the VBA window, click on Run, then Reset.

You might also want to double-click on Modules in the Project Explorer
(press ctrl+R first, if necessary), then double-click on the normal module
name that contains the makeDistrib code.


"sumesh56" wrote:
everytime i search for the necessary lines in the postings
to quote the link with the excelbanter is going.
[....] please suggest how can i insert quote tag wherever i want?
[....]
in your feedback some lines are in different color say green.
how do you accomplish this?

These are excelbanter.com usage questions. I cannot answer them because I
do not use excelbanter.com.

I did set the color of text. Excelbanter.com is doing that automatically.

thank you very much for the help. now i am able to make the distribution sheet by running your macro. have a nice day.

Errata.... I wrote:
I did set the color of text.
Excelbanter.com is doing that automatically.

I think you understood me correctly, but for posterity....

I meant to write: I did __not__ set the color of text. Klunk!