test mark
 

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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200711/1

test mark
 

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" wrote:

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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200711/1

test mark
 

ISNA?

Joel wrote:
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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200711/1

test mark
 

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.

"Darrell_Sarrasin via OfficeKB.com" wrote:

ISNA?

Joel wrote:
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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200711/1

test mark
 

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

Joel wrote:
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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200711/1

test mark
 

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

"Darrell_Sarrasin via OfficeKB.com" wrote:

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

Joel wrote:
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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200711/1

test mark
 

thanks I think I can work with that :)


Joel wrote:
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]
If this is easier done using a input box, by all mean suggest that, but the
hard code is appreciated as well.

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