test mark

[Darrell_Sarrasin via OfficeKB.com]

I currently have two work sheets in an excel document and I need to create a
chart to read the one sheet and fill in the other sheet with either an "x" or
the mark.

as an example the information is on sheet1 and I need it to read the persons
name, and the course and then post it onto a second sheet called report.

here is where it gets a little tricky. sheet 1 has 40000 lines so I need the
macro to read through the 40000 lines and locate the name and then compair it
to the secondary cell that has the course. for example lets say john smith
in column A and the course Final Test in Column B. I then need the
information to post either the "X" or the grade in the cell "D4" on sheet 2.

If this is easier done using a input box, by all mean suggest that, but the
hard code is appreciated as well.

 

[Joel]

this is probably best done by using a vlookup on sheet2

=IF(ISNA(VLOOKUP(A1,Sheet1!A1:B40000,2)),"","X")

 

[Darrell_Sarrasin via OfficeKB.com]

ISNA?

this is probably best done by using a vlookup on sheet2

=IF(ISNA(VLOOKUP(A1,Sheet1!A1:B40000,2)),"","X")

I currently have two work sheets in an excel document and I need to create a
chart to read the one sheet and fill in the other sheet with either an "x" or

[quoted text clipped - 11 lines]

If this is easier done using a input box, by all mean suggest that, but the
hard code is appreciated as well.

 

[Joel]

VLOOKUP returns an NA error if it can't find the data. ISNA detects the error.

The ISNA will allow "" (nothing) to be displayed on the worksheet if data is
not found. If data is found an X will appear.

ISNA?

this is probably best done by using a vlookup on sheet2

=IF(ISNA(VLOOKUP(A1,Sheet1!A1:B40000,2)),"","X")

I currently have two work sheets in an excel document and I need to create a
chart to read the one sheet and fill in the other sheet with either an "x" or

[quoted text clipped - 11 lines]

If this is easier done using a input box, by all mean suggest that, but the
hard code is appreciated as well.

 

[Darrell_Sarrasin via OfficeKB.com]

and if I want to use this in a macro how would it work. I have 40 agents
with 30 plus classes to do. example "john" is the name "certification" is
the course

VLOOKUP returns an NA error if it can't find the data. ISNA detects the error.

The ISNA will allow "" (nothing) to be displayed on the worksheet if data is
not found. If data is found an X will appear.

ISNA?

[quoted text clipped - 7 lines]

 

[Joel]

You weren't specific about the sheet names and the column. the code below
will need some minor adjustments. The code checks every row in column A on
the Report worksheets and then does a search on Sheet 1 Column A for the
name. If it finds the name it copies the data in Column B to Column D.

Sub FindGrades()
With Sheets("Report")
ReportRow = 1
Do While .Range("A" & ReportRow) <> ""
Name = .Range("A" & ReportRow)
With Sheets("Sheet1")
Set c = .Columns("A:A").Find(what:=Name, LookIn:=xlValues)
End With
If Not c Is Nothing Then
.Range("D" & ReportRow) = c.Offset(rowoffset:=0, columnoffset:=1)
End If
ReportRow = ReportRow + 1
Loop
End With
End Sub

and if I want to use this in a macro how would it work. I have 40 agents
with 30 plus classes to do. example "john" is the name "certification" is
the course

VLOOKUP returns an NA error if it can't find the data. ISNA detects the error.

The ISNA will allow "" (nothing) to be displayed on the worksheet if data is
not found. If data is found an X will appear.

ISNA?

[quoted text clipped - 7 lines]

If this is easier done using a input box, by all mean suggest that, but the
hard code is appreciated as well.

 

[Darrell_Sarrasin via OfficeKB.com]

thanks I think I can work with that

You weren't specific about the sheet names and the column. the code below
will need some minor adjustments. The code checks every row in column A on
the Report worksheets and then does a search on Sheet 1 Column A for the
name. If it finds the name it copies the data in Column B to Column D.

Sub FindGrades()
With Sheets("Report")
ReportRow = 1
Do While .Range("A" & ReportRow) <> ""
Name = .Range("A" & ReportRow)
With Sheets("Sheet1")
Set c = .Columns("A:A").Find(what:=Name, LookIn:=xlValues)
End With
If Not c Is Nothing Then
.Range("D" & ReportRow) = c.Offset(rowoffset:=0, columnoffset:=1)
End If
ReportRow = ReportRow + 1
Loop
End With
End Sub

and if I want to use this in a macro how would it work. I have 40 agents
with 30 plus classes to do. example "john" is the name "certification" is

[quoted text clipped - 10 lines]