Hi guys I work in a school and they asked me to tell them which
students got the total full mark in all subjects, my problem is every
subject have its own excel workbook file, the students are sorted by
class then by IDs, from subject to subject the file may be different (I
tried referencing and it didn't work as expected) like some grades may
not take a particular subject this means that this subject file will
have a missing rows so I thought I could make a macro to do the
following:
- check the ID of the student
- get the total marks in all workbooks for that student depending on ID
and add them together
- copy the students with total full mark to a new workbook
- loop until it finish all the students IDs
I am not that good in excel VBA but do you think this could be done
using macro or am I going the wrong path?

note: I thought about using the search for ID approach instead of just
using cell offset because they are going to use the macro with
different files later on that may be a complete mess and the ID is the
only thing we can relay on.

Hi Talista,

FWIW, yes this all sounds perfectly feasible for a VBA project. Looks
like your facing a steep learning curve though. ?;^)

NickH

Sorry Talista,

My previous response was rather unhelpful.

If your serious about learning VBA I can recommend the Power
Programming books by John Walkenbach. Excellent for all levels of
experience.

And here are some useful links...

http://www.mrexcel.com/archive/VBA/index.html

http://www.vba-programmer.com/#ExcelBkmk

http://www.rondebruin.nl/tips.htm

http://www.exceltip.com/category.html#6


Also Chip Pearson provides an excellent resource...

http://www.cpearson.com

Kind regards, NickH

Hi NickH,
Thanks for the links I found it really informative, it seems like this
task is beyond my basic VBA skills but I'll try to do it my only
problem is with the deadline, anyways am stuck in something now, how do
you search for a number in all workbooks? If you have an answer please
do because I am kind of lost here :)
NickH wrote:
Sorry Talista,

My previous response was rather unhelpful.

If your serious about learning VBA I can recommend the Power
Programming books by John Walkenbach. Excellent for all levels of
experience.

And here are some useful links...

http://www.mrexcel.com/archive/VBA/index.html

http://www.vba-programmer.com/#ExcelBkmk

http://www.rondebruin.nl/tips.htm

http://www.exceltip.com/category.html#6


Also Chip Pearson provides an excellent resource...

http://www.cpearson.com

Kind regards, NickH

Assuming all the required workbooks are open, and assuming you are
doing the sensible thing and keeping your application workbook (with
the code in) separate from the data, you can loop through the open
workbooks like so...

Dim wbk as Workbook
Dim wks as Worksheet

' Loop through open workbooks
For each wbk in Application.Workbooks
'Ignore application workbook
if wbk < Thisworkbook then

'Loop through worksheets in each workbook
For each wks in wbk.Worksheets
'Put your search code etc. in here
'
'

Next wks

End If
Next wbk


That's enough for now - I mean you wouldn't want me to do the whole
thing for you would you. ?;^)

Kind regards, NickH