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

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

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

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

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