Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
looping through columns and rows
Hello,
Can someone show me the code which would allow me to search through a number of rows and columns, to identify a criteria. Each time the criteria is found, 1 is added to a variable. When the variable reaches a set number, the search moves to a different column. This would allow me to allocate people to jobs according to their first, second, third choice etc, without resourcing too many people to a job. can you help? I have very little experience, but am usually able to see what is happening in the code and expend on it to take care of more elements. kind regards, Matt |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
looping through columns and rows
something like this for i=1 to cells(rows.count,"a").end(xlup).row if cells(i,"a")="criteria" then mc=mc+1 next i msgbox mc -- Don Guillett SalesAid Software "Guerilla" wrote in message oups.com... Hello, Can someone show me the code which would allow me to search through a number of rows and columns, to identify a criteria. Each time the criteria is found, 1 is added to a variable. When the variable reaches a set number, the search moves to a different column. This would allow me to allocate people to jobs according to their first, second, third choice etc, without resourcing too many people to a job. can you help? I have very little experience, but am usually able to see what is happening in the code and expend on it to take care of more elements. kind regards, Matt |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
looping through columns and rows
On Mar 20, 9:38 am, "Guerilla" wrote:
Hello, Can someone show me the code which would allow me to search through a number of rows and columns, to identify a criteria. Each time the criteria is found, 1 is added to a variable. When the variable reaches a set number, the search moves to a different column. This would allow me to allocate people to jobs according to their first, second, third choice etc, without resourcing too many people to a job. can you help? I have very little experience, but am usually able to see what is happening in the code and expend on it to take care of more elements. kind regards, Matt It seems as if you need a nested For...Next loop. Something to the following might work (given your description): Option Explicit Sub allocateResources() Dim counter As Integer 'or Long Dim c As Variant 'column "bookmark" Dim r As Variant 'row "bookmark" counter = 0 'the outer loop moves through columns 'the inner (or nested loop) moves through the rows For c = 1 To 20 For r = 1 To 40 If (Cells(r,c).Value = (some criteria)) Then 'code 'counter = counter + 1 End If If counter = (some number) Then Exit For End If Next Next End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
looping through columns and rows
Thanks for the help. I am still a bit confused however.
The spreadsheet will be layed out in a table fasion. the rows will contain the person's name and their 5 choics (6 cells in total). There will be about 40 names in a list. I need to programe to look at the first person and their first choice, check to see if their choice is available and then allocate them to it (perhaps by highlighting their choice in green. If there 1st chioce is not available their second choice would be considered, this would be in the same row but 1 column to the right. Once a person has been allocated to a job, their other choices become redundant, so the program should move to the next person (one column below the original). As mentioned before, if a job has 10 places, the check would need to see if the job has been fullt allocated. Anyone who has not managed to secure a job would need their name highligted in red. I hope I have explained that well enough. I could email a simple copy of the file to anyone who may be able to help. kind regards, Matt |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
looping through columns and rows
On Mar 22, 10:40 am, "Guerilla" wrote:
Thanks for the help. I am still a bit confused however. The spreadsheet will be layed out in a table fasion. the rows will contain the person's name and their 5 choics (6 cells in total). There will be about 40 names in a list. I need to programe to look at the first person and their first choice, check to see if their choice is available and then allocate them to it (perhaps by highlighting their choice in green. If there 1st chioce is not available their second choice would be considered, this would be in the same row but 1 column to the right. Once a person has been allocated to a job, their other choices become redundant, so the program should move to the next person (one column below the original). As mentioned before, if a job has 10 places, the check would need to see if the job has been fullt allocated. Anyone who has not managed to secure a job would need their name highligted in red. I hope I have explained that well enough. I could email a simple copy of the file to anyone who may be able to help. kind regards, Matt Matt, You have the code in the nested For loop. Simply set up an If...Then Statement or a Case Statement to evaluate your criteria. Search VBE Help for "If...Then Statement," "Select Case Statement," and "GoTo Method" and you should see some helpful information. The outer loop moves through the row, and the inner loop moves through the column For r = 1 To 40 'the row number of the employee For c = 2 To 6 'the column number of the column value If (Cells(r,c).Value = (some criteria)) Then 'code End If If (allocated) Then GoTo Allocated End If If Cells(r,c).Value = "" Then 'tests for a blank cell Exit For 'this will exit the column loop End If Next Allocated: Next You can test a blank cell with IsEmpty(range/value) or with something like Cells(r,c).Value = "". As for colors, use the macro recorder or search "ColorIndex Property/ColorIndex Property as it applies to the Interior object" in Excel VBA. For example, you can code something like this: Range("A1").Interior.ColorIndex = 3. Matt |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
looping through rows and columns | Excel Discussion (Misc queries) | |||
Looping macro needed to find intersections of rows and columns | Excel Programming | |||
Looping through Columns then Rows | Excel Programming | |||
Looping thru columns | Excel Programming | |||
Looping thru columns beyond Z | Excel Programming |