Look for names when cursor is there..

[Elton Law]

Dear expert,

I have a file containing 2 sheets

Summary = Names and Marks
Calculation = Contains the breakdown of Marks

Say in Summary sheet.
A1 to B15 are like this.

Names Marks
Chrain 81
Teri 77
Jim 75
Ricky 74
Trudy 74
Pat 68
James 67
Mark 63
Peggy 62
Michael 61
Fred 55
Kim 53
Ray 45
Fiona 42

Say I highlight the cell of Ricky. Then press (use Mouse) a button (will be
done later in cell C1) next to Marks (cell B1).

It can automatically go to search Ricky in sheet calculation.
Say I highlight (cursor) the cell of Peggy, it can automatically go to
search Peggy in sheet calculation. I can see the breakdown then.

The fact is that .... The sequence in Summary can be changed from time to
time.
Secondly, names are more than 232 (I copy/paste few here).
I don't want to set macro for 232 names and search individual names by
clicking individual buttons next to their marks.

Just want to click one button at the cell C1 and if I highlight the cell of
Ray, then search in sheet calculation and go to look for Ray's breakdown of
marks.
That can save space and can time.
Also, this can be customized for next year (different people each year)

Thanks,

 

[JLGWhiz]

You really only need one button. View>Toolbars>Control Toolbox. Click on
the command button then place the crosshairs where you want the button on
the sheet with the names and marks The button will scroll with the sheet
unless you freeze the first two or three lines and put the button on the
frozen pane. Then while in dexign mode, right click the butoon, then select
view code from the drop down menu. Paste this code into the code window
that opens:

Private Sub CommandButton1_Click()
Dim c As Range
Set c = Sheets("Calculation").Cells.Find(Selection, LookIn:=xlValues)
If Not c Is Nothing then
c.Row.Select
Else
MsgBox "Student name not found. Check Spelling",
,"ADVISORY"
End If
End Sub

Before you click the button, you must select the student's name that you
want to review. You can only do one at a time. The names must be identical
on both sheets.

 

[Elton Law]

Hi Friend,
Thanks for reply.

I followed what you asked.
When I ran, it gave a window saying compile error. Invalid qualifier.

These are highlighted in blue.
..Row

Do you know what is the problem?
Thanks

 

[Jacob Skaria]

Try the modified 'JLGWhiz''s code.

Dim c As Range
Set c = Sheets("Calculation").Cells.Find(Selection, LookIn:=xlValues)

If Not c Is Nothing Then
Application.Goto Sheets("calculation").Range("A" & c.Row)
c.EntireRow.Select
Else
MsgBox "Student name not found. Check Spelling", , "ADVISORY"
End If

 

[Elton Law]

Hi Jacob,
You often helped me .... Thanks indeed. It works now .... Tks always.