Hi i am looking for help to combine some programming.

Basically what I have so far is I have two excel worksheets once that is
called Report, the other that is called Completions. What the coding below
does is it looks in two columns on Report worksheet. one is the name of the
person and that sits in column A, and it has courses they completed in column
B. From there it goes to the completions Worksheet and cross references by
looking at class courses across the top and the names down the left hand side
of the students. Right now if the report has a course that the student has
completed it puts a "C" in a cell and keeps going through the report
worksheet untill all of the students and classes from the Completions
worksheet have been checked.

What I want it to do is also check in column F to see if the courses they did
are in either a completed status or a started status. if it has started then
I want it to display an S and if it is completed I want it to show a C.

This is what I have right now:

Option Explicit
Dim rColARep As Range, rColAComp As Range, rRow1Comp As Range
Dim i As Range, TheRow As Long, TheCol As Long
Dim CancelA As Boolean



Sub CourseCompletion()
Application.ScreenUpdating = False
Sheets("Report").Select
Set rColARep = Range("A2", Range("A" & Rows.Count).End(xlUp))
With Sheets("Completions")
Set rColAComp = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
Set rRow1Comp = .Range("B1", .Cells(1, Columns.Count).End
(xlToLeft))
For Each i In rColARep
If Not IsEmpty(i.Offset(, 1).Value) Then
Call ChkName
If CancelA = True Then GoTo SkipName
Call ChkCourse
If CancelA = True Then GoTo SkipName
TheRow = rColAComp.Find(What:=i.Value, LookAt:
=xlWhole).Row
TheCol = rRow1Comp.Find(What:=i.Offset(, 1).Value,
LookAt:=xlWhole).Column
.Cells(TheRow, TheCol).Value = "C"
End If
SkipName:
Next i
End With
Sheets("Completions").Select
Application.ScreenUpdating = True
Range("B2:AP179").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlTop
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End Sub

Sub ChkName()
CancelA = False
If rColAComp.Find(What:=i.Value, LookAt:=xlWhole) Is Nothing Then
CancelA = True
End If
End Sub

Sub ChkCourse()
CancelA = False
If rRow1Comp.Find(What:=i.Offset(, 1).Value, LookAt:=xlWhole) Is
Nothing Then
CancelA = True
End If
End Sub

--
Message posted via http://www.officekb.com