Help Required - Allocation based on exclusion of values

[Santhi28595]

Hi team,

I have finally completed the attached macro however in the task code I am updating text instead of a number, however the code is showing error. Can anyone help me to fix the error.


I believe the current coding looks out for numbers in the task code however I am trying to modify values to text.

I have attached the error screen shot.

Kindly help me to get this code fixed.

[Marc L]

Hi !

Classic typo : replace 1 by L …

[Fluff13]

With this line

    ky = sh1.Cells(r1, "E") + 1

You are trying to add 1 to a text value, which you cannot do. try removing the part in red

[Santhi28595]

With this line

    ky = sh1.Cells(r1, "E") + 1

You are trying to add 1 to a text value, which you cannot do. try removing the part in red

I removed that as well however still the values that I enter in task code is not excluding...

[Santhi28595]

Hi Marc,

Thank you. Even though if replaced still the task codes are not excluded while assigning.

Can u check and tell me.

Thanks a lot for the quick assistance

[Fluff13]

Can you please explain what you are trying to do & what is not working?

[Santhi28595]

Hi Fluff,

The macro is designed in such a way that it Excludes the values given in task code and product code sheets And assigns the remaining data to the users in the assigned summary.

So ideally it should look first on column E and check in task code sheet, if the same value is there it should return excluded Task code in column J.

Hope u understood now

[Fluff13]

Hope u understood now

Fraid not.
Please remember that we have no knowledge of what you are trying to do, or what your data represents.

[Santhi28595]

Fraid not.
Please remember that we have no knowledge of what you are trying to do, or what your data represents.

Hi Fluff,

This macro assigns data after excluding the values that is given in task code and product code.

However currently it is excluding only the values that was given in product code.

However it does not exclude the values that was given in task code, please let me know if there is any changes that to be done in my code to get this worked.

[Fluff13]

Until you can supply me with a full, clear & concise description of what you are trying to do, I cannot help

[Santhi28595]

Fluff,

This is a assignment macro. I have modified in such a way that.

1. It Excludes the values that is provided in the task code sheet.(based on column E)
2. Then it Excludes the values that is provided in the product code sheet. (Based on column F)

So after this exclusion, the reamining data which is in the data tab gets assigned to the users.

Let me Know if u still have any questions.

[Fluff13]

All you are doing is repeating what you have already said, which I don't understand.

Imagine you were standing in a queue at a ticket office & were trying to explain to the person next to you, what you were trying to do.
That is the sort of description that I need.

[Santhi28595]

Hi Fluff

Sorry the objective of the macro is to assign data to the users.

This is macro which equally assigns data to the users.

Can you please let me what u did not understood so I can provide explanation on that.

[Santhi28595]

While assigning I have 2 check points to be checked.

1. Task code (column D). The macro looks for the data that is provided in the task code tab and excludes whatever the values that was there in the first colu.

2. Once task code exclusion is over then, it looks for values in product code and compare with the values in the product code sheet. If it contains same values, it excludes those row in the data and returns a value in column J as "Excluded product code"

The reamining data only after these 2 exclusion should be assigned to the users in the team.

[Santhi28595]

I want to know is there any modifications that need to be done so that exclusion of task code works.

[Pepe Le Mokko]

Thread title much more descriptive. Thanks

[Santhi28595]

Thread title much more descriptive. Thanks

Thank you. Any help is much more appreciated !!

[Santhi28595]

Hi, thanks for the reminder. I will.modify that.

[bakerman2]

I think I understand what you're looking for but I have 1 question.

What happens if all persons have a case allocated but there are still rows to process. Do you start again at the top of the Name list.

Can 1 person be assigned multiple cases ?

[Santhi28595]

In this case, whoever is in the next list in the assigned summary will be picked up so it gets evenly distributed.

Say for example, if the last case allocated to "Alan' then when I again put the data it gets allocated to "Harita"

My coding works like that however only the exclusion in task code doesn't work. Not sure if any format has to be changed.

[bakerman2]

Try this one.

[Santhi28595]

Try this one.

Hi bakerman,

Thanks for the effort but I still don't see any changes that happened. The macro does not exclude the task code and it simply assigns everything.

Product code is getting excluded however task code is not excluding.Can u please check

[bakerman2]

Your sheet variables don't work.

[Santhi28595]

Your sheet variables don't work.

Hi bakerman,

This really works out. Thanks a lot.

Have u changed the entire code? Also I have a small doubt if I do a vlookup with the task code number as that is only available in my raw data. Will the same macro work? Or should I always have the values in text format..


I have attached the file. Please review and let me know ur feedback.

[bakerman2]

Don't understand what you're trying to achieve.

Explain clearly the steps you want to take.

[Santhi28595]

Hi bakerman,

Please refer to my attached file now.I will have only task code number, each task code number has a unique name. So whatever the file u gave me has the direct task code values in it.

If I do a vlookup with task code number and return the task code values, does the macro works??

I want to know if the macro still works with a vlookup function. You refer to my attached sheet where I have returned the task code names for the corresponding task code number.

[bakerman2]

Is this what you have in mind ?

[Santhi28595]

Is this what you have in mind ?

Hi I believe you have replaced it by task code number. What I exactly require is to compare the names provided in the task code sheet.

My question is that each task code name carries a number.

If I do a vlookup with task code number (column E) and return the values in column F.

Would that same macro works. Now what I need is compare the vales of F in it the task code sheet and assign accordingly.

Would that be possible?

[bakerman2]

Just shift all column letters by 1.

Sub assign()

Dim sh3 As Worksheet

r1 = 2: rw = 4
r2 = Sheet1.Range("B" & Rows.Count).End(xlUp).Row

For j = 4 To r2
  If Sheet1.Cells(j, "D") = "" Then
   Sheet1.Cells(rw, "M") = j
   Sheet1.Cells(rw, "N") = Sheet1.Cells(j, "C")
   rw = rw + 1
  End If
Next
r2 = Sheet1.Range("N" & Rows.Count).End(xlUp).Row

While Sheet2.Cells(r1, 1) <> ""
    If Sheet2.Cells(r1, "I") = "" Then
        ky = Sheet2.Cells(r1, "B")
        Set srch = Sheet2.Range("B2:B" & r1).Find(ky, LookIn:=xlValues)
        xrow = srch.Row
        If srch.Row = r1 Then
            ky = Sheet2.Cells(r1, "F")
            xx = Application.Match(ky, Sheet9.Columns(1), 0)
            If IsError(xx) Then
                ky = Sheet2.Cells(r1, "G")
                xx = Application.Match(ky, Sheet3.Columns(1), 0)
                If IsError(xx) Then
                    nxt = WorksheetFunction.Min(Sheet1.Range("N4:N" & r2))
                    nx = WorksheetFunction.Match(nxt, Sheet1.Range("N4:N" & r2), 0) + 3
                    nxt = Sheet1.Cells(nx, "M")
                    Sheet1.Cells(nxt, "C") = Sheet1.Cells(nxt, "C") + 1
                    Sheet1.Cells(nx, "N") = Sheet1.Cells(nx, "N") + 1
                    Sheet2.Cells(r1, "J") = Sheet1.Cells(nxt, "B")
                    wsn = Sheet1.Cells(nxt, "B")
                    Set sh3 = Sheets(wsn)
                    r3 = sh3.Range("B" & Rows.Count).End(xlUp).Row + 1
                    For c = 1 To 4
                        sh3.Cells(r3, c) = Sheet2.Cells(r1, c)
                    Next
                Else
                    Sheet2.Cells(r1, "K") = "** Excluded Product **"
                End If
            Else
                Sheet2.Cells(r1, "K") = "** Excluded Task Code **"
            End If
        Else
            Set sh3 = Sheets("Errors")
            r3 = sh3.Range("B" & Rows.Count).End(xlUp).Row + 1
            For c = 1 To 4
                sh3.Cells(r3, c) = Sheet2.Cells(r1, c)
            Next
            Sheet2.Cells(r1, "J") = Sheet2.Cells(xrow, "J")
            Sheet2.Cells(r1, "K") = "** Duplicate Case Number **"
        End If
    End If
    r1 = r1 + 1
Wend
End Sub
Sub restart()

Set sh1 = Sheets("DATA")
Set sh2 = Sheets("Assigned Summary")

sh1.Range("I2:K1000").ClearContents
sh2.Range("M4:n1000").ClearContents
r2 = 4
While sh2.Cells(r2, "B") <> ""
sh2.Cells(r2, "C") = 0

r2 = r2 + 1
Wend

For s = Sheets.Count - 5 To Sheets.Count - 1
Sheets(s).Range("A2:E1000").ClearContents
Next
End Sub

[Santhi28595]

Hi bakerman,
I am sorry I could not understand. Shift by 1 means u want me to move all the columns???

[bakerman2]

The updated code is in my previous post.

[Santhi28595]

The updated code is in my previous post.

I copied to my file, however when I try to add new value, in the task code exclusion list, the code didn't work. Can u check that for me please.

Appreciate all the efforts taken to get my queries solved.

[bakerman2]

Works just fine for me. Send file where the error occurs.

[Santhi28595]

Works just fine for me. Send file where the error occurs.

Hi bakerman, please refer attached. No cases gets assigned even if I paste the updated macro.

Also if there is #NA during a vlookup, it means that those are not excluded and it should get assigned.

Also if the case gets duplicated again, can we able to assign to the same person to whom it was assigned before.

Please review and share your feedback.

[bakerman2]

1. It's more then logical that nothing gets assigned because you have either an Excluded Task Code or Product Code in every row.

2. How can you assign a task if information isn't complete ? Do you really want that to happen ?

3. This is for a new thread.

Also please stop quoting posts as it just clutters the thread.

[Santhi28595]

Hi bakerman,

If you refer to my data there is one row #NA, which means that it is not Excluded. It should get assigned right?.

Also if your statement tells that it was all excluded, then remarks should be updated as per the category of exclusion.

Can you please share your Excel where it works perfect so I can check and confirm.

[bakerman2]

I have downloaded your file from Post#34 again and noticed that Column I is filled all the way down.

Then you have added the condition

If Sheet2.Cells(r1, "I") = "" Then

but the point is that column I isn't empty on any row ??????? thus code aborts execution on every loop.

This is going nowhere if you keep adding things without thinking them through.

[Santhi28595]

I just copy pasted the code that u have given in your earlier post.

I have not modified anything else. Can u share ur Excel which works fine so I can check again.

[Santhi28595]

Hi bakerman,

I just ran the macro with column "I" as empty, the macro works. Can u tell why does column "I" should be empty???

Is the design is like that??

Also one more doubt is that, if I add a new data and click assign the loop does not work from where it stopped
Do we need to add any specific code to continue the loop.

[bakerman2]

Previously I just looked at the Columns but now my remark still stands.

If Sheet2.Cells(r1, "I") = "" Then

Code will never work because Segment Column isn't empty on any row.

It's not the design, it's something you added yourself.

[Santhi28595]

Hi Bakerman,

I agree to your point and I made the segment column as empty and the macro works.

Now if I add new data to the sheet, and click assign, the loop works indefinitely, the macro assigns repeatedly on how much I click the assign button.

Can u check and let me know if I add any code to break the loop.

[Santhi28595]

Hi bakerman,

The macro works fine now. Really thank you for all the efforts taken to get it sorted.

Duplicates alone is not working, it does not return the same name which was assigned earlier before, I will try to fix it, if there is any code to altered, please let me know.

Thanks a ton for the support !!!!