lookup a row# on another sheet with a find option?

DetroitDr

New Member
Joined
Sep 27, 2006
Messages
9
Hello,
I'm trying to keep attendance of students. My workbook has only 2 sheets. Sheet 1 has all of the students information
eg
Student ID#,First name, last name, age, grade.... and attendance which is column Q (also student id# is unique)


Sheet 2 is nothing more then a page to enter the students unique ID # so I can Vlookup their personal data to enter on a template for printing a lable. ...This works great!

ISSUE: When I print the lable from within sheet two, I'd like to update attendance of the child on sheet 1 by adding 1 to the attendance value perhaps using [QXX].Value = [QXX] +1

The only way I think I can do this is to use a Find command on Sheet 1 to look at column A for the unique ID# entered on Sheet2 then get the row# and concatenate column Q(attendance) with theRow# and then make the value of QROW#=Qrow# + 1 (on sheet1). I'm not a VB programmer but know a little and hope someone can provide any insight or an easier way of doing this. ..even perhaps a VB script?


Thank-you in advance.

George Miller
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

agihcam

Well-known Member
Joined
Jan 16, 2006
Messages
1,624
ISSUE: When I print the lable from within sheet two, I'd like to update attendance of the child on sheet 1 by adding 1 to the attendance value perhaps using [QXX].Value = [QXX] +1

how was the printing process being done? is it manual from the file > print or you already have a macro for the printing process?
 

DetroitDr

New Member
Joined
Sep 27, 2006
Messages
9
Thank-you for your reply!

Printing is being done via a Button assigned to a macro.
Macro for printing is:

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

So I just need to add to this to get the attendance updated.

Thank-you again!!!
 

agihcam

Well-known Member
Joined
Jan 16, 2006
Messages
1,624
ok. add this line of codes to your existing button codes;
Code:
With Sheets("Sheet1").Columns("a") 'change sheetname as reuqired
    Set c = .Find(Sheets("Sheet2").Range("a1").Value, , , xlWhole) 'change where the actual ID# is located in sheet2
        If Not c Is Nothing Then
            c.Offset(, 16).Value = c.Offset(, 16).Value + 1
        End If
End With
 

DetroitDr

New Member
Joined
Sep 27, 2006
Messages
9
agihcam,

Thank-you VERY Much this has helped Greatly!!!

Again much appreciated!
 

Forum statistics

Threads
1,136,699
Messages
5,677,273
Members
419,683
Latest member
MrVBAConfused

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top