• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

help me with the coding

shuthairah

shuthairah

New Member
hello,
i have some coding that i didn't really know why it can it work
the task is to assign variable to slot with weight
i want to assign the exam with the highest no. of student clashes to the time slot with the highest weight
i get the code but it didn't run well
can anyone help with the coding
the code is
Sub Macro2()
Dim TA, TB As Long


For TB = 1 To Range("Y5").Column - Range("H5").Column + 1


For TA = Range("H5").Column To Range("Y5").Column


If Cells(5, TA) = WorksheetFunction.Large(Range("H5:Y5"), TB) Then
Cells(6, TA) = WorksheetFunction.Large(Range("B2:B25"), TB)
End If


If Cells(7, TA) = WorksheetFunction.Large(Range("H7:Y7"), TB) Then
Cells(8, TA) = WorksheetFunction.Large(Range("B2:B25"), TB)
End If


Next TA
Next TB
End Sub

it do assign to column but have some problem, went i try the code msg box will came out and said
"run-time error 1004: unable to get the Large property of worksheetfunction class"
and when i click debug it highlight
"If Cells(7, TA) = WorksheetFunction.Large(Range("H7:Y7"), TB) Then"

can anyone help with the coding please
 

Attachments

Hi Shuthairah,

The syntax for the Microsoft Excel LARGE function is: LARGE( array, nth_position )
See http://www.techonthenet.com/excel/formulas/large.php

The error message means that one (or both) of the parameters (array and nth position) are causing an error.

Your code works fine so I would guess that it is something to do with the setup of your worksheet.

Can you upload the workbook file please?

Thanks,

Peter
 
Hi Shuthairah,

It looks like the Report part of your worksheet started off in the range H5:Y8 and has subsequently been moved. The VBA is therefore looking at the wrong part part of the worksheet.

I have moved it back to H5:Y8 (and added three zeros in cells W7:Y7) and attached the file.

Peter
 

Attachments

its works, thanks..
if i want to change from using number to use the exam (e.g, e1, e2 etc) from column one so which part of the coding that i have to change??
 
Try this:

Code: 
    If Cells(5, TA) = WorksheetFunction.Large(Range("H5:Y5"), TB) Then
      iStudentClashes = WorksheetFunction.Large(Range("B2:B25"), TB)
   
      With Application.WorksheetFunction
        Cells(6, TA) = .Index(Range("A2:A25"), .Match(iStudentClashes, Range("B2:B25"), 0))
      End With
    End If

Remember to declare the new variable iStudentClashes as long at the top of your procedure.
 
i try and msg box came out and said " run-time error 1004: application-defined or objective define error" and highlight "If Cells(5, TA) = WorksheetFunction.Large(Range("H5:Y5"), TB) Then"

do i have to change all the coding?? or just pasted the code that u give above the previous one??

i declare the variable like this

Dim iStudentClashes As Long
 
Hi Shuthairah,

Very happy to help.

If you have a new query, please post this as a new thread in the forum as this is likely to attract more replies.

Thanks,

Peter
 
its on the same problem...
i fee really grateful for ur help..
but last problem...how can i put all the data without repeating the same answer..
the result didn't use all the data in table exam..
the coding that u give are very helpful..
i change a little so that the exam fill all the timeslot but some exam is repeating
Dim TA As Long, TB As Long

For TB = 1 To Range("Y5").Column - Range("H5").Column + 1

For TA = Range("H5").Column To Range("Y5").Column


If Cells(5, TA) = WorksheetFunction.Large(Range("H5:Y5"), TB) Then
iStudentClashes = WorksheetFunction.Large(Range("B2:B34"), TB)

With Application.WorksheetFunction
Cells(6, TA) = .Index(Range("A2:A34"), .Match(iStudentClashes, Range("B2:B34"), 0))
End With
End If


If Cells(7, TA) = WorksheetFunction.Large(Range("H7:Y7"), TB) Then
iStudentClashes = WorksheetFunction.Large(Range("B2:B34"), TB)

With Application.WorksheetFunction
Cells(8, TA) = .Index(Range("A2:A34"), .Match(iStudentClashes, Range("B2:B34"), 0))
End With
End If


Next TA

Next TB

how can i make it use all the data without repeating the same exam in another time slot

this is the file
 

Attachments

Do you mean that all time slots (morning and evening) need to be unique so that the exam is allocated only once across all 36 time slots?
 
yes.. according to the weight..
the exam with the highest no. of student clashes will be on the 1st time slot with the highest weight..
 
Hmm, that is a much more complicated question and I think is going to involve some intermediate level VBA. The reason I say this is that in your table of Exams and Student Clashes, you have the same number of student clashes (47) referring to two Exams (e8 and e21). This means that a simple lookup using worksheet formulae isn't going to do the job.

Give me a bit of time and I will come back to you.
 
thank you so much for ur help..if it difficult i dont want to burden u, its ok not to do it..
but if u want to help it will be my pleasure..thank you so much
 
I think this will now work. The VBA is still relatively straightforward (looping through ranges). I have also added a couple of helper columns.

Updated file attached.

Code: 
Sub AllocateExams()

Dim iStudentClashes As Long, iClassCount As Long
Dim r As Long
Dim rngWeightings As Range, rngMorning As Range, rngEvening As Range
Dim rngExams As Range, rngStudentClashes As Range
Dim rngWeightingsTable As Range, rngExamAllocation As Range
Dim rngCell As Range

'It is easier to see how forumlas are constructed if we use 'Names'
'for ranges.  Because a range is an object (in Excel / VBA language)
'we must preface it with the word Set
Set rngExams = Range("A2:A34")
Set rngStudentClashes = Range("B2:B34")
Set rngMorning = Range("H5:Y5")
Set rngEvening = Range("H7:Y7")

'A single range that has all the cells (weightings) for morning and evening in it
Set rngWeightings = Union(rngMorning, rngEvening)

Set rngWeightingsTable = Range("D2:D" & rngWeightings.Cells.Count)
Set rngExamAllocation = Range("E2:E" & rngWeightings.Cells.Count)


'Loop through each cell and write it to the Weightings table
For Each rngCell In rngWeightings
  r = r + 1
  rngWeightingsTable.Cells(r, 1).Value = rngCell.Value
Next rngCell

'Sort the weightings in descending order
  With ThisWorkbook.Worksheets("Sheet2").Sort
    .SortFields.Clear
    .SortFields.Add Key:=rngWeightingsTable.Resize(1, 1), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    .SetRange rngWeightingsTable
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
  End With


'Loop through each cell in the weightings table and allocate an exam
For Each rngCell In rngWeightingsTable
  iClassCount = iClassCount + 1

  'When we have allocated all of the classes then we need to stop looking
  If iClassCount >= rngStudentClashes.Cells.Count Then Exit For

  iStudentClashes = WorksheetFunction.Large(rngStudentClashes, iClassCount)

  With Application.WorksheetFunction
    rngCell.Offset(0, 1).Value = rngExams.Cells(rngExams.Rows.Count - iClassCount, 1).Value
  End With

Next rngCell


'Loop through each weightings cell in the timetable and allocate the corresponding exam
For Each rngCell In rngWeightings
  With Application.WorksheetFunction
    rngCell.Offset(1, 0).Value = .Index(rngExamAllocation, .Match(rngCell.Value, rngWeightingsTable, 0))
  End With
Next rngCell


End Sub
 

Attachments

You must log in or register to reply here.
Back
Top