Assign person to defined percentage of rows2019 Community Moderator ElectionExcel VBA to determine last non-value (IE may have a formula but no value) row in columnExcel VBA - read cell value from codedynamic rows and columns in VBA ChartCopy data from worksheet to html file to mailExcel Pivot depending upon Header valuesConditional copy Excel File-2 data to excel file-1?VBA script to copy adjacent cells on same row if duplicate foundhow to read same data from two sheet then subtract the data using vbaTake the date in one worksheet and find the same date in another worksheet column and return the cell reference for that date to use in a loopDynamic VBA Delete based on cell not containing Part of a text
How does signal strength relate to bandwidth?
Why did the Cray-1 have 8 parity bits per word?
Rationale to prefer local variables over instance variables?
Split a number into equal parts given the number of parts
What is better: yes / no radio, or simple checkbox?
“I had a flat in the centre of town, but I didn’t like living there, so …”
Make me a metasequence
Where is this quote about overcoming the impossible said in "Interstellar"?
Where is the fallacy here?
Being asked to review a paper in conference one has submitted to
Was it really inappropriate to write a pull request for the company I interviewed with?
It doesn't matter the side you see it
Can a Trickery Domain cleric cast a spell through the Invoke Duplicity clone while inside a Forcecage?
PTIJ: What dummy is the Gemara referring to?
Reason why dimensional travelling would be restricted
Misplaced tyre lever - alternatives?
How do you say “my friend is throwing a party, do you wanna come?” in german
How to fix my table, centering of columns
Is there a full canon version of Tyrion's jackass/honeycomb joke?
Did Amazon pay $0 in taxes last year?
Lock enemy's y-axis when using Vector3.MoveTowards to follow the player
If nine coins are tossed, what is the probability that the number of heads is even?
Why do phishing e-mails use faked e-mail addresses instead of the real one?
Giving a talk in my old university, how prominently should I tell students my salary?
Assign person to defined percentage of rows
2019 Community Moderator ElectionExcel VBA to determine last non-value (IE may have a formula but no value) row in columnExcel VBA - read cell value from codedynamic rows and columns in VBA ChartCopy data from worksheet to html file to mailExcel Pivot depending upon Header valuesConditional copy Excel File-2 data to excel file-1?VBA script to copy adjacent cells on same row if duplicate foundhow to read same data from two sheet then subtract the data using vbaTake the date in one worksheet and find the same date in another worksheet column and return the cell reference for that date to use in a loopDynamic VBA Delete based on cell not containing Part of a text
1
I'm currently creating a distribution tool in order to distribute person to tasks based on the defined percentage for each person. Below is the sample data for the assignee and with their respective distribution percentage which can be found in my "Main Sheet". The assignee can grow as long as the total of the distribution will be 100%.
|---------------------|--------------------------------|
| Assignee | Distribution Percentage |
|---------------------|--------------------------------|
| Person1 | 25 |
|---------------------|--------------------------------|
| Person2 | 30 |
|---------------------|--------------------------------|
| Person2 | 45 |
|---------------------|--------------------------------|
In another sheet called "New" I have a list of tasks which needs to be assigned to the person according to its defined percentage. And Sometimes, there is already an assigned person, in this case, to skip assigning to that task.
Below also shows the list of tasks and expected output (person assigned) based on the defined percentage for the distribution. The tasks can also grow:
|---------------------|--------------------------------|
| Assignee | Tasks |
|---------------------|--------------------------------|
| Person1 | Task 1 |
|---------------------|--------------------------------|
| Person1 | Task 2 |
|---------------------|--------------------------------|
| Person1 | Task 3 |
|---------------------|--------------------------------|
| Person1 | Task 4 |
|---------------------|--------------------------------|
| Person2 | Task 5 |
|---------------------|--------------------------------|
| Person2 | Task 6 |
|---------------------|--------------------------------|
| Person2 | Task 7 |
|---------------------|--------------------------------|
| Person2 | Task 8 |
|---------------------|--------------------------------|
| Person3 | Task 9 |
|---------------------|--------------------------------|
| Person3 | Task 10 |
|---------------------|--------------------------------|
| Person3 | Task 11 |
|---------------------|--------------------------------|
| Person3 | Task 12 |
|---------------------|--------------------------------|
| Person3 | Task 13 |
|---------------------|--------------------------------|
| Person3 | Task 14 |
|---------------------|--------------------------------|
| Person3 | Task 15 |
|---------------------|--------------------------------|
In this case, below are the distribution:
Person1 - 4 Tasks (25%)
Person2 - 4 Tasks (30%)
Person3 - 7 Tasks (45%)
Below is working code that I have. However, it doesn't meet the output I need. And I'm getting stuck on how to proceed:
Sub AssignPercentage()
Dim PersonFirstRow As Integer
Dim PersonLastRow As Long
Dim PersonRow As Long
Set mainSheet = Sheets("Main")
Set TodaySheet = Sheets("New")
Dim LastRow As Long, LastColumn As Long
Dim StartCell As Range, rng As Range
Dim x As Long
Dim cl As Range
Dim Percentage As Long, i As Long
Dim PersonPercent As Long
Set StartCell = TodaySheet.Range("B2")
PersonFirstRow = 10 'row of F12
PersonLastRow = mainSheet.Cells(mainSheet.Rows.Count, "E").End(xlUp).Row
LastRow = TodaySheet.Cells(TodaySheet.Rows.Count, StartCell.Column).End(xlUp).Row
LastColumn = TodaySheet.Cells(StartCell.Row, TodaySheet.Columns.Count).End(xlToLeft).Column
Set rng = TodaySheet.Range(StartCell, TodaySheet.Cells(LastRow, 2))
For x = PersonFirstRow To PersonLastRow
PersonPercent = mainSheet.Cells(x, "F").Value
Percentage = Round(rng.Rows.Count * PersonPercent / 100, 0)
For Each cl In rng
i = i + 1
If i > Percentage Then
i = 0
Exit For
End If
If Trim(cl.Offset(0, -1).Value) = "" Then
cl.Offset(0, -1).Value = mainSheet.Cells(x, "E").Value
End If
Next cl
Next x
End Sub
The output of the code above is below, which is not correct:
|---------------------|--------------------------------|
| Assignee | Tasks |
|---------------------|--------------------------------|
| Person1 | Task 1 |
|---------------------|--------------------------------|
| Person1 | Task 2 |
|---------------------|--------------------------------|
| Person1 | Task 3 |
|---------------------|--------------------------------|
| Person1 | Task 4 |
|---------------------|--------------------------------|
| Person3 | Task 5 |
|---------------------|--------------------------------|
| Person3 | Task 6 |
|---------------------|--------------------------------|
| Person3 | Task 7 |
|---------------------|--------------------------------|
| | Task 8 |
|---------------------|--------------------------------|
| | Task 9 |
|---------------------|--------------------------------|
| | Task 10 |
|---------------------|--------------------------------|
| | Task 11 |
|---------------------|--------------------------------|
| | Task 12 |
|---------------------|--------------------------------|
| | Task 13 |
|---------------------|--------------------------------|
| | Task 14 |
|---------------------|--------------------------------|
| | Task 15 |
|---------------------|--------------------------------|
excel vba
asked 21 hours ago
SevpointSevpoint
94110
add a comment |
1
I'm currently creating a distribution tool in order to distribute person to tasks based on the defined percentage for each person. Below is the sample data for the assignee and with their respective distribution percentage which can be found in my "Main Sheet". The assignee can grow as long as the total of the distribution will be 100%.
|---------------------|--------------------------------|
| Assignee | Distribution Percentage |
|---------------------|--------------------------------|
| Person1 | 25 |
|---------------------|--------------------------------|
| Person2 | 30 |
|---------------------|--------------------------------|
| Person2 | 45 |
|---------------------|--------------------------------|
In another sheet called "New" I have a list of tasks which needs to be assigned to the person according to its defined percentage. And Sometimes, there is already an assigned person, in this case, to skip assigning to that task.
Below also shows the list of tasks and expected output (person assigned) based on the defined percentage for the distribution. The tasks can also grow:
|---------------------|--------------------------------|
| Assignee | Tasks |
|---------------------|--------------------------------|
| Person1 | Task 1 |
|---------------------|--------------------------------|
| Person1 | Task 2 |
|---------------------|--------------------------------|
| Person1 | Task 3 |
|---------------------|--------------------------------|
| Person1 | Task 4 |
|---------------------|--------------------------------|
| Person2 | Task 5 |
|---------------------|--------------------------------|
| Person2 | Task 6 |
|---------------------|--------------------------------|
| Person2 | Task 7 |
|---------------------|--------------------------------|
| Person2 | Task 8 |
|---------------------|--------------------------------|
| Person3 | Task 9 |
|---------------------|--------------------------------|
| Person3 | Task 10 |
|---------------------|--------------------------------|
| Person3 | Task 11 |
|---------------------|--------------------------------|
| Person3 | Task 12 |
|---------------------|--------------------------------|
| Person3 | Task 13 |
|---------------------|--------------------------------|
| Person3 | Task 14 |
|---------------------|--------------------------------|
| Person3 | Task 15 |
|---------------------|--------------------------------|
In this case, below are the distribution:
Person1 - 4 Tasks (25%)
Person2 - 4 Tasks (30%)
Person3 - 7 Tasks (45%)
Below is working code that I have. However, it doesn't meet the output I need. And I'm getting stuck on how to proceed:
Sub AssignPercentage()
Dim PersonFirstRow As Integer
Dim PersonLastRow As Long
Dim PersonRow As Long
Set mainSheet = Sheets("Main")
Set TodaySheet = Sheets("New")
Dim LastRow As Long, LastColumn As Long
Dim StartCell As Range, rng As Range
Dim x As Long
Dim cl As Range
Dim Percentage As Long, i As Long
Dim PersonPercent As Long
Set StartCell = TodaySheet.Range("B2")
PersonFirstRow = 10 'row of F12
PersonLastRow = mainSheet.Cells(mainSheet.Rows.Count, "E").End(xlUp).Row
LastRow = TodaySheet.Cells(TodaySheet.Rows.Count, StartCell.Column).End(xlUp).Row
LastColumn = TodaySheet.Cells(StartCell.Row, TodaySheet.Columns.Count).End(xlToLeft).Column
Set rng = TodaySheet.Range(StartCell, TodaySheet.Cells(LastRow, 2))
For x = PersonFirstRow To PersonLastRow
PersonPercent = mainSheet.Cells(x, "F").Value
Percentage = Round(rng.Rows.Count * PersonPercent / 100, 0)
For Each cl In rng
i = i + 1
If i > Percentage Then
i = 0
Exit For
End If
If Trim(cl.Offset(0, -1).Value) = "" Then
cl.Offset(0, -1).Value = mainSheet.Cells(x, "E").Value
End If
Next cl
Next x
End Sub
The output of the code above is below, which is not correct:
|---------------------|--------------------------------|
| Assignee | Tasks |
|---------------------|--------------------------------|
| Person1 | Task 1 |
|---------------------|--------------------------------|
| Person1 | Task 2 |
|---------------------|--------------------------------|
| Person1 | Task 3 |
|---------------------|--------------------------------|
| Person1 | Task 4 |
|---------------------|--------------------------------|
| Person3 | Task 5 |
|---------------------|--------------------------------|
| Person3 | Task 6 |
|---------------------|--------------------------------|
| Person3 | Task 7 |
|---------------------|--------------------------------|
| | Task 8 |
|---------------------|--------------------------------|
| | Task 9 |
|---------------------|--------------------------------|
| | Task 10 |
|---------------------|--------------------------------|
| | Task 11 |
|---------------------|--------------------------------|
| | Task 12 |
|---------------------|--------------------------------|
| | Task 13 |
|---------------------|--------------------------------|
| | Task 14 |
|---------------------|--------------------------------|
| | Task 15 |
|---------------------|--------------------------------|
excel vba
asked 21 hours ago
SevpointSevpoint
94110
When you sayit doesn't meet the output I need, what is it that you get?
– Zac
20 hours ago
I have edited the question and included the current output of the code. It only assigned persons until task seven. Which should be the same on the expected output above.
– Sevpoint
20 hours ago
The problem seems to be that for the second person the 'For each cl in rng' loop starts at 1 again
– AjCorneel
19 hours ago
add a comment |
1
1
1
I'm currently creating a distribution tool in order to distribute person to tasks based on the defined percentage for each person. Below is the sample data for the assignee and with their respective distribution percentage which can be found in my "Main Sheet". The assignee can grow as long as the total of the distribution will be 100%.
|---------------------|--------------------------------|
| Assignee | Distribution Percentage |
|---------------------|--------------------------------|
| Person1 | 25 |
|---------------------|--------------------------------|
| Person2 | 30 |
|---------------------|--------------------------------|
| Person2 | 45 |
|---------------------|--------------------------------|
In another sheet called "New" I have a list of tasks which needs to be assigned to the person according to its defined percentage. And Sometimes, there is already an assigned person, in this case, to skip assigning to that task.
Below also shows the list of tasks and expected output (person assigned) based on the defined percentage for the distribution. The tasks can also grow:
|---------------------|--------------------------------|
| Assignee | Tasks |
|---------------------|--------------------------------|
| Person1 | Task 1 |
|---------------------|--------------------------------|
| Person1 | Task 2 |
|---------------------|--------------------------------|
| Person1 | Task 3 |
|---------------------|--------------------------------|
| Person1 | Task 4 |
|---------------------|--------------------------------|
| Person2 | Task 5 |
|---------------------|--------------------------------|
| Person2 | Task 6 |
|---------------------|--------------------------------|
| Person2 | Task 7 |
|---------------------|--------------------------------|
| Person2 | Task 8 |
|---------------------|--------------------------------|
| Person3 | Task 9 |
|---------------------|--------------------------------|
| Person3 | Task 10 |
|---------------------|--------------------------------|
| Person3 | Task 11 |
|---------------------|--------------------------------|
| Person3 | Task 12 |
|---------------------|--------------------------------|
| Person3 | Task 13 |
|---------------------|--------------------------------|
| Person3 | Task 14 |
|---------------------|--------------------------------|
| Person3 | Task 15 |
|---------------------|--------------------------------|
In this case, below are the distribution:
Person1 - 4 Tasks (25%)
Person2 - 4 Tasks (30%)
Person3 - 7 Tasks (45%)
Below is working code that I have. However, it doesn't meet the output I need. And I'm getting stuck on how to proceed:
Sub AssignPercentage()
Dim PersonFirstRow As Integer
Dim PersonLastRow As Long
Dim PersonRow As Long
Set mainSheet = Sheets("Main")
Set TodaySheet = Sheets("New")
Dim LastRow As Long, LastColumn As Long
Dim StartCell As Range, rng As Range
Dim x As Long
Dim cl As Range
Dim Percentage As Long, i As Long
Dim PersonPercent As Long
Set StartCell = TodaySheet.Range("B2")
PersonFirstRow = 10 'row of F12
PersonLastRow = mainSheet.Cells(mainSheet.Rows.Count, "E").End(xlUp).Row
LastRow = TodaySheet.Cells(TodaySheet.Rows.Count, StartCell.Column).End(xlUp).Row
LastColumn = TodaySheet.Cells(StartCell.Row, TodaySheet.Columns.Count).End(xlToLeft).Column
Set rng = TodaySheet.Range(StartCell, TodaySheet.Cells(LastRow, 2))
For x = PersonFirstRow To PersonLastRow
PersonPercent = mainSheet.Cells(x, "F").Value
Percentage = Round(rng.Rows.Count * PersonPercent / 100, 0)
For Each cl In rng
i = i + 1
If i > Percentage Then
i = 0
Exit For
End If
If Trim(cl.Offset(0, -1).Value) = "" Then
cl.Offset(0, -1).Value = mainSheet.Cells(x, "E").Value
End If
Next cl
Next x
End Sub
The output of the code above is below, which is not correct:
|---------------------|--------------------------------|
| Assignee | Tasks |
|---------------------|--------------------------------|
| Person1 | Task 1 |
|---------------------|--------------------------------|
| Person1 | Task 2 |
|---------------------|--------------------------------|
| Person1 | Task 3 |
|---------------------|--------------------------------|
| Person1 | Task 4 |
|---------------------|--------------------------------|
| Person3 | Task 5 |
|---------------------|--------------------------------|
| Person3 | Task 6 |
|---------------------|--------------------------------|
| Person3 | Task 7 |
|---------------------|--------------------------------|
| | Task 8 |
|---------------------|--------------------------------|
| | Task 9 |
|---------------------|--------------------------------|
| | Task 10 |
|---------------------|--------------------------------|
| | Task 11 |
|---------------------|--------------------------------|
| | Task 12 |
|---------------------|--------------------------------|
| | Task 13 |
|---------------------|--------------------------------|
| | Task 14 |
|---------------------|--------------------------------|
| | Task 15 |
|---------------------|--------------------------------|
excel vba
asked 21 hours ago
SevpointSevpoint
94110
I'm currently creating a distribution tool in order to distribute person to tasks based on the defined percentage for each person. Below is the sample data for the assignee and with their respective distribution percentage which can be found in my "Main Sheet". The assignee can grow as long as the total of the distribution will be 100%.
|---------------------|--------------------------------|
| Assignee | Distribution Percentage |
|---------------------|--------------------------------|
| Person1 | 25 |
|---------------------|--------------------------------|
| Person2 | 30 |
|---------------------|--------------------------------|
| Person2 | 45 |
|---------------------|--------------------------------|
In another sheet called "New" I have a list of tasks which needs to be assigned to the person according to its defined percentage. And Sometimes, there is already an assigned person, in this case, to skip assigning to that task.
Below also shows the list of tasks and expected output (person assigned) based on the defined percentage for the distribution. The tasks can also grow:
|---------------------|--------------------------------|
| Assignee | Tasks |
|---------------------|--------------------------------|
| Person1 | Task 1 |
|---------------------|--------------------------------|
| Person1 | Task 2 |
|---------------------|--------------------------------|
| Person1 | Task 3 |
|---------------------|--------------------------------|
| Person1 | Task 4 |
|---------------------|--------------------------------|
| Person2 | Task 5 |
|---------------------|--------------------------------|
| Person2 | Task 6 |
|---------------------|--------------------------------|
| Person2 | Task 7 |
|---------------------|--------------------------------|
| Person2 | Task 8 |
|---------------------|--------------------------------|
| Person3 | Task 9 |
|---------------------|--------------------------------|
| Person3 | Task 10 |
|---------------------|--------------------------------|
| Person3 | Task 11 |
|---------------------|--------------------------------|
| Person3 | Task 12 |
|---------------------|--------------------------------|
| Person3 | Task 13 |
|---------------------|--------------------------------|
| Person3 | Task 14 |
|---------------------|--------------------------------|
| Person3 | Task 15 |
|---------------------|--------------------------------|
In this case, below are the distribution:
Person1 - 4 Tasks (25%)
Person2 - 4 Tasks (30%)
Person3 - 7 Tasks (45%)
Below is working code that I have. However, it doesn't meet the output I need. And I'm getting stuck on how to proceed:
Sub AssignPercentage()
Dim PersonFirstRow As Integer
Dim PersonLastRow As Long
Dim PersonRow As Long
Set mainSheet = Sheets("Main")
Set TodaySheet = Sheets("New")
Dim LastRow As Long, LastColumn As Long
Dim StartCell As Range, rng As Range
Dim x As Long
Dim cl As Range
Dim Percentage As Long, i As Long
Dim PersonPercent As Long
Set StartCell = TodaySheet.Range("B2")
PersonFirstRow = 10 'row of F12
PersonLastRow = mainSheet.Cells(mainSheet.Rows.Count, "E").End(xlUp).Row
LastRow = TodaySheet.Cells(TodaySheet.Rows.Count, StartCell.Column).End(xlUp).Row
LastColumn = TodaySheet.Cells(StartCell.Row, TodaySheet.Columns.Count).End(xlToLeft).Column
Set rng = TodaySheet.Range(StartCell, TodaySheet.Cells(LastRow, 2))
For x = PersonFirstRow To PersonLastRow
PersonPercent = mainSheet.Cells(x, "F").Value
Percentage = Round(rng.Rows.Count * PersonPercent / 100, 0)
For Each cl In rng
i = i + 1
If i > Percentage Then
i = 0
Exit For
End If
If Trim(cl.Offset(0, -1).Value) = "" Then
cl.Offset(0, -1).Value = mainSheet.Cells(x, "E").Value
End If
Next cl
Next x
End Sub
The output of the code above is below, which is not correct:
|---------------------|--------------------------------|
| Assignee | Tasks |
|---------------------|--------------------------------|
| Person1 | Task 1 |
|---------------------|--------------------------------|
| Person1 | Task 2 |
|---------------------|--------------------------------|
| Person1 | Task 3 |
|---------------------|--------------------------------|
| Person1 | Task 4 |
|---------------------|--------------------------------|
| Person3 | Task 5 |
|---------------------|--------------------------------|
| Person3 | Task 6 |
|---------------------|--------------------------------|
| Person3 | Task 7 |
|---------------------|--------------------------------|
| | Task 8 |
|---------------------|--------------------------------|
| | Task 9 |
|---------------------|--------------------------------|
| | Task 10 |
|---------------------|--------------------------------|
| | Task 11 |
|---------------------|--------------------------------|
| | Task 12 |
|---------------------|--------------------------------|
| | Task 13 |
|---------------------|--------------------------------|
| | Task 14 |
|---------------------|--------------------------------|
| | Task 15 |
|---------------------|--------------------------------|
excel vba
excel vba
asked 21 hours ago
SevpointSevpoint
94110
asked 21 hours ago
SevpointSevpoint
94110
edited 20 hours ago
Sevpoint
asked 21 hours ago
SevpointSevpoint
94110
asked 21 hours ago
SevpointSevpoint
94110
asked 21 hours ago
SevpointSevpoint
94110
94110
When you sayit doesn't meet the output I need, what is it that you get?
– Zac
20 hours ago
I have edited the question and included the current output of the code. It only assigned persons until task seven. Which should be the same on the expected output above.
– Sevpoint
20 hours ago
The problem seems to be that for the second person the 'For each cl in rng' loop starts at 1 again
– AjCorneel
19 hours ago
add a comment |
When you sayit doesn't meet the output I need, what is it that you get?
– Zac
20 hours ago
I have edited the question and included the current output of the code. It only assigned persons until task seven. Which should be the same on the expected output above.
– Sevpoint
20 hours ago
The problem seems to be that for the second person the 'For each cl in rng' loop starts at 1 again
– AjCorneel
19 hours ago
When you say
it doesn't meet the output I need, what is it that you get?– Zac
20 hours ago
When you say
it doesn't meet the output I need, what is it that you get?– Zac
20 hours ago
I have edited the question and included the current output of the code. It only assigned persons until task seven. Which should be the same on the expected output above.
– Sevpoint
20 hours ago
I have edited the question and included the current output of the code. It only assigned persons until task seven. Which should be the same on the expected output above.
– Sevpoint
20 hours ago
The problem seems to be that for the second person the 'For each cl in rng' loop starts at 1 again
– AjCorneel
19 hours ago
The problem seems to be that for the second person the 'For each cl in rng' loop starts at 1 again
– AjCorneel
19 hours ago
add a comment |
1 Answer
1
active
oldest
votes
0
If you swap to looping through the task assignments and checking whether enough tasks have been assigned yet, it might be easier to make it work. The below code does the job for me.
Dim PersonFirstRow As Integer
Dim PersonLastRow As Long
Dim PersonRow As Long
Set mainsheet = Sheets("Main")
Set todaysheet = Sheets("New")
Dim LastRow As Long, LastColumn As Long
Dim StartCell As Range, rng As Range
Dim Percentage As Long, i As Long
Dim PersonPercent As Long
Dim TaskRow, AssignedTasks As Long
Set StartCell = todaysheet.Range("B2")
PersonFirstRow = 10 'row of F12
PersonLastRow = mainsheet.Cells(mainsheet.Rows.Count, "E").End(xlUp).Row
LastRow = todaysheet.Cells(todaysheet.Rows.Count, StartCell.Column).End(xlUp).Row
LastColumn = todaysheet.Cells(StartCell.Row, todaysheet.Columns.Count).End(xlToLeft).Column
Set rng = todaysheet.Range(StartCell, todaysheet.Cells(LastRow, 2))
For TaskRow = 2 To LastRow
For PersonRow = 10 To PersonLastRow
PersonPercent = mainsheet.Cells(PersonRow, "F").Value
Percentage = Round(rng.Rows.Count * PersonPercent / 100, 0)
AssignedTasks = Application.WorksheetFunction.CountIf(rng.Offset(0, -1), mainsheet.Cells(PersonRow, 5).Value)
If AssignedTasks + 1 <= Percentage Then
If Trim(todaysheet.Cells(TaskRow, 1).Value) = "" Then
todaysheet.Cells(TaskRow, 1).Value = mainsheet.Cells(PersonRow, "E").Value
End If
End If
Next PersonRow
Next TaskRow
End Sub
answered 16 hours ago
AjCorneelAjCorneel
611
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55020704%2fassign-person-to-defined-percentage-of-rows%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
If you swap to looping through the task assignments and checking whether enough tasks have been assigned yet, it might be easier to make it work. The below code does the job for me.
Dim PersonFirstRow As Integer
Dim PersonLastRow As Long
Dim PersonRow As Long
Set mainsheet = Sheets("Main")
Set todaysheet = Sheets("New")
Dim LastRow As Long, LastColumn As Long
Dim StartCell As Range, rng As Range
Dim Percentage As Long, i As Long
Dim PersonPercent As Long
Dim TaskRow, AssignedTasks As Long
Set StartCell = todaysheet.Range("B2")
PersonFirstRow = 10 'row of F12
PersonLastRow = mainsheet.Cells(mainsheet.Rows.Count, "E").End(xlUp).Row
LastRow = todaysheet.Cells(todaysheet.Rows.Count, StartCell.Column).End(xlUp).Row
LastColumn = todaysheet.Cells(StartCell.Row, todaysheet.Columns.Count).End(xlToLeft).Column
Set rng = todaysheet.Range(StartCell, todaysheet.Cells(LastRow, 2))
For TaskRow = 2 To LastRow
For PersonRow = 10 To PersonLastRow
PersonPercent = mainsheet.Cells(PersonRow, "F").Value
Percentage = Round(rng.Rows.Count * PersonPercent / 100, 0)
AssignedTasks = Application.WorksheetFunction.CountIf(rng.Offset(0, -1), mainsheet.Cells(PersonRow, 5).Value)
If AssignedTasks + 1 <= Percentage Then
If Trim(todaysheet.Cells(TaskRow, 1).Value) = "" Then
todaysheet.Cells(TaskRow, 1).Value = mainsheet.Cells(PersonRow, "E").Value
End If
End If
Next PersonRow
Next TaskRow
End Sub
answered 16 hours ago
AjCorneelAjCorneel
611
add a comment |
0
If you swap to looping through the task assignments and checking whether enough tasks have been assigned yet, it might be easier to make it work. The below code does the job for me.
Dim PersonFirstRow As Integer
Dim PersonLastRow As Long
Dim PersonRow As Long
Set mainsheet = Sheets("Main")
Set todaysheet = Sheets("New")
Dim LastRow As Long, LastColumn As Long
Dim StartCell As Range, rng As Range
Dim Percentage As Long, i As Long
Dim PersonPercent As Long
Dim TaskRow, AssignedTasks As Long
Set StartCell = todaysheet.Range("B2")
PersonFirstRow = 10 'row of F12
PersonLastRow = mainsheet.Cells(mainsheet.Rows.Count, "E").End(xlUp).Row
LastRow = todaysheet.Cells(todaysheet.Rows.Count, StartCell.Column).End(xlUp).Row
LastColumn = todaysheet.Cells(StartCell.Row, todaysheet.Columns.Count).End(xlToLeft).Column
Set rng = todaysheet.Range(StartCell, todaysheet.Cells(LastRow, 2))
For TaskRow = 2 To LastRow
For PersonRow = 10 To PersonLastRow
PersonPercent = mainsheet.Cells(PersonRow, "F").Value
Percentage = Round(rng.Rows.Count * PersonPercent / 100, 0)
AssignedTasks = Application.WorksheetFunction.CountIf(rng.Offset(0, -1), mainsheet.Cells(PersonRow, 5).Value)
If AssignedTasks + 1 <= Percentage Then
If Trim(todaysheet.Cells(TaskRow, 1).Value) = "" Then
todaysheet.Cells(TaskRow, 1).Value = mainsheet.Cells(PersonRow, "E").Value
End If
End If
Next PersonRow
Next TaskRow
End Sub
answered 16 hours ago
AjCorneelAjCorneel
611
add a comment |
0
0
0
If you swap to looping through the task assignments and checking whether enough tasks have been assigned yet, it might be easier to make it work. The below code does the job for me.
Dim PersonFirstRow As Integer
Dim PersonLastRow As Long
Dim PersonRow As Long
Set mainsheet = Sheets("Main")
Set todaysheet = Sheets("New")
Dim LastRow As Long, LastColumn As Long
Dim StartCell As Range, rng As Range
Dim Percentage As Long, i As Long
Dim PersonPercent As Long
Dim TaskRow, AssignedTasks As Long
Set StartCell = todaysheet.Range("B2")
PersonFirstRow = 10 'row of F12
PersonLastRow = mainsheet.Cells(mainsheet.Rows.Count, "E").End(xlUp).Row
LastRow = todaysheet.Cells(todaysheet.Rows.Count, StartCell.Column).End(xlUp).Row
LastColumn = todaysheet.Cells(StartCell.Row, todaysheet.Columns.Count).End(xlToLeft).Column
Set rng = todaysheet.Range(StartCell, todaysheet.Cells(LastRow, 2))
For TaskRow = 2 To LastRow
For PersonRow = 10 To PersonLastRow
PersonPercent = mainsheet.Cells(PersonRow, "F").Value
Percentage = Round(rng.Rows.Count * PersonPercent / 100, 0)
AssignedTasks = Application.WorksheetFunction.CountIf(rng.Offset(0, -1), mainsheet.Cells(PersonRow, 5).Value)
If AssignedTasks + 1 <= Percentage Then
If Trim(todaysheet.Cells(TaskRow, 1).Value) = "" Then
todaysheet.Cells(TaskRow, 1).Value = mainsheet.Cells(PersonRow, "E").Value
End If
End If
Next PersonRow
Next TaskRow
End Sub
answered 16 hours ago
AjCorneelAjCorneel
611
If you swap to looping through the task assignments and checking whether enough tasks have been assigned yet, it might be easier to make it work. The below code does the job for me.
Dim PersonFirstRow As Integer
Dim PersonLastRow As Long
Dim PersonRow As Long
Set mainsheet = Sheets("Main")
Set todaysheet = Sheets("New")
Dim LastRow As Long, LastColumn As Long
Dim StartCell As Range, rng As Range
Dim Percentage As Long, i As Long
Dim PersonPercent As Long
Dim TaskRow, AssignedTasks As Long
Set StartCell = todaysheet.Range("B2")
PersonFirstRow = 10 'row of F12
PersonLastRow = mainsheet.Cells(mainsheet.Rows.Count, "E").End(xlUp).Row
LastRow = todaysheet.Cells(todaysheet.Rows.Count, StartCell.Column).End(xlUp).Row
LastColumn = todaysheet.Cells(StartCell.Row, todaysheet.Columns.Count).End(xlToLeft).Column
Set rng = todaysheet.Range(StartCell, todaysheet.Cells(LastRow, 2))
For TaskRow = 2 To LastRow
For PersonRow = 10 To PersonLastRow
PersonPercent = mainsheet.Cells(PersonRow, "F").Value
Percentage = Round(rng.Rows.Count * PersonPercent / 100, 0)
AssignedTasks = Application.WorksheetFunction.CountIf(rng.Offset(0, -1), mainsheet.Cells(PersonRow, 5).Value)
If AssignedTasks + 1 <= Percentage Then
If Trim(todaysheet.Cells(TaskRow, 1).Value) = "" Then
todaysheet.Cells(TaskRow, 1).Value = mainsheet.Cells(PersonRow, "E").Value
End If
End If
Next PersonRow
Next TaskRow
End Sub
answered 16 hours ago
AjCorneelAjCorneel
611
answered 16 hours ago
AjCorneelAjCorneel
611
answered 16 hours ago
AjCorneelAjCorneel
611
answered 16 hours ago
AjCorneelAjCorneel
611
611
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55020704%2fassign-person-to-defined-percentage-of-rows%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
When you say
it doesn't meet the output I need, what is it that you get?– Zac
20 hours ago
I have edited the question and included the current output of the code. It only assigned persons until task seven. Which should be the same on the expected output above.
– Sevpoint
20 hours ago
The problem seems to be that for the second person the 'For each cl in rng' loop starts at 1 again
– AjCorneel
19 hours ago