I have a workbook containing about 40 sheets for each employee. Within
each sheet, there is a chart such as this:

date------------- training 1----training 2------training 3 ...........
??/??/????-------------X
??/??/????---------------------------------------X
??/??/????-------------X
.........

I need to automate a process which will scan a sheet (I -don't mind-
running a macro on each sheet manually) and will retrieve training that
the employee DOES NOT yet have, and input it in some sort of chart
format.

In other words, from the above example, training 2 has not be done by
the employee, and so the outputted chart will show me the employee name
(or the sheet name) and the training that they have to complete.



Thank you!


--
aposatsk
------------------------------------------------------------------------
aposatsk's Profile: http://www.excelforum.com/member.php...o&userid=36709
View this thread: http://www.excelforum.com/showthread...hreadid=564892

Option Explicit
Sub test()
Dim iRowPtr As Integer, iClmPtr As Integer
Dim iFirstRow As Integer, iLastRow
Dim iKount As Integer, iSumRow As Integer
Dim sTrainNum(1 To 4) As String, sMissed As String

iFirstRow = r1
iLastRow = r2
iSumRow = 1

sTrainNum(1) = "Training1"
sTrainNum(2) = "Training2"
sTrainNum(3) = "Training3"
sTrainNum(4) = "Training4"

For iRowPtr = iFirstRow To iLastRow
iKount = 0
sMissed = ""
For iClmPtr = 2 To 5
If Cells(iRowPtr, iClmPtr).Value = "" Then
iKount = iKount + 1
sMissed = sMissed & sTrainNum(iClmPtr - 1) & "/"
End If
Next iClmPtr
If iKount 0 Then
Worksheets(3).Cells(iSumRow, 1).Value = Cells(iRowPtr,
1).Value
Worksheets(3).Cells(iSumRow, 2).Value = sMissed
iSumRow = iSumRow + 1End If
Next iRowPtr

End Sub


--
protonLeah


------------------------------------------------------------------------
protonLeah's Profile: http://www.excelforum.com/member.php...o&userid=32097
View this thread: http://www.excelforum.com/showthread...hreadid=564892

Could you explain to me how that works please?


--
aposatsk
------------------------------------------------------------------------
aposatsk's Profile: http://www.excelforum.com/member.php...o&userid=36709
View this thread: http://www.excelforum.com/showthread...hreadid=564892

From the diagram in your initial query, I assumed that you have a chart
comprized of a "date" column, and several "training-x" columns.

The heart of the macro is comprised of two "FOR" loops: an outer loop
steps through the ROWS of the chart; the inner loop steps through the
training COLUMNS of the current date row as indicated by your example.

Since you indicated you are interested in missed training, this inner
loop checks the cell in the current training column for the presence of
a training indicator, an "x" in your example. If the column is empty, a
report fragment is assembled for that date by appending the "Training
Title" and a slash mark separator to and a counter is incremented (
the counter (or flag) indicated that missed training was encountered).
Then the next column is examined.

When the last column for the current date has been processed, the
counter is tested. If it is greater than zero, then a line item of the
report is created on a separate sheet, #3 in this sample macro,
consisting of the date in column 1 and and the missed training titles
in column 2, e.g.,

mm/dd/yy...training1/training4/

then the report row pointer is incremented to prepare for the next
report item.

------------------------------------------------------------
set-up
------------------------------------------------------------
sTrainNum is the array that holds the training titles (4 in the example
macro)

You have to code the first and last rows of the chart that contain data
to be scanned, alternatively, the macro could be coded to ask you for
the values.


--
protonLeah


------------------------------------------------------------------------
protonLeah's Profile: http://www.excelforum.com/member.php...o&userid=32097
View this thread: http://www.excelforum.com/showthread...hreadid=564892