Any ideas how to proceed ? 2

[itsfisko]

Hi all I wonder if anyone can give me some ideas of if and how this might work, and if I am in the right forum.

I am trying to devise a script or a form in excll to do this..

Have an input box to choose a user (from excell)(they all are numbers)ie 04001 then an input box to give a mark or grade ie (5)
An operation button to then place the mark into a cell in a work book opposite the user.
I imagine the code would check the workbook to find the user number and then using this information place it into a defined cell. If any one has any input I would be gratfull
John Stressed school tech

Some lead, some follow....I just Hope!

 

[trollacious]

Show what kind of code you have so far, and then we can go from there.

 

[itsfisko]

I would but its rubbish!
I can just about get it to produce the input boxes so any suggestions would help
Thanks

Some lead, some follow....I just Hope!

 

[infinitelo]

'------------------------------------------------
'This assumes
' the first data row is 1
' the idnumbers are in column A
' the grades are in column B
' a blank cell indicates end of data
' script does not save or close workbook
'------------------------------------------------
idnumber = inputbox("input id number")
grade = inputbox("input grade")
dim objXL
set objXl = WScript.CreateObject("Excel.Application")
objXL.workbooks.open("c:\fullpath\filename.xls")
objXL.visible = True
objxl.sheets("nameofsheet").activate

i = 1 'row number
done = "false"
do while done = "false"
if objxl.cells(i,1) = idnumber then
objxl.cells(i,2).value = grade
done = idnumber & " Grade Inserted"
end if
if objxl.cells(i,1) = "" then
done = idnumber & " Not Found"
end if
i = i + 1
loop
msgbox(done)
'save your workbook and close

if it is to be it's up to me

 

[itsfisko]

This looks good infinitelo, I will input my parameters and give it a try.
Thanks John Stressed School Tech

Some lead, some follow....I just Hope!

 

[itsfisko]

Hi i've just had a look at the code, it is a great start. The grades I need to be in columns from H to M so I will need to have an input box to choose the column for the grade.
The users are spread over 4 sheets ie Year 5, Year 6, Year 7, Year 8 and start at different rows!! I know its a nightmare...
I did a test using sheet, year 6 ie:
objxl.sheets("year 6").activate
with no success it retuned an error on the number not found.
Is it possible to get it to search the whole Workbook, find the Unique ID number and place the mark in that row, in a column as specified from the input box?
Thanks for your help I will try to fiddle it but any input is always helpful for us slow learners.
Thanks once again John Stressed school tech ;-)



Some lead, some follow....I just Hope!

 

[infinitelo]

oops cant do = ""
----------------------------------
if not(objxl.cells(i,1) > " ") then
done = idnumber & " Not Found"
end if
----------------------------------

as for checking all sheets
maybe something like this???
this will need adjustment as it always checks all sheets.
--------------------------------------
x = objxl.sheets.count
for i = 1 to x
objxl.sheets(i).activate
'search code goes here
next

----------------------------------------
also you may consider adding a data entry sheet.
instead of input boxes.

so if you have a lot of grades to enter, you can
enter them all into one sheet and loop through
each row, search , if found update cell and delete entry,
if not found indicate it. then you can add them to the
proper sheet

hint:
objxl.sheets(sheetnumberorname).cells(row,column)

This is all pretty well "off the cuff" you may want to use visual basic for applications, as it has help, and on xp or later intellisense(<< huge help)

after you learn more about execl application model move to
script for portability.


if it is to be it's up to me

 

[itsfisko]

Thanks for your help I am wrestling with it now, However I still get a number not found even when the work book contains that number, It appears to be searching.....
Thanks again John slghtly puzzled school tech

Some lead, some follow....I just Hope!

 

[infinitelo]

oops again forgot input box needs to be converted to
integer, didnt check syntax before posting.
this might help a lot.
sorry for the bad code.


if cint(idnumber) = objxl.cells(i,1).value then


if it is to be it's up to me

 

[itsfisko]

Hi still tweaking

I now have this

dnumber = inputbox("input id number")
grade = inputbox("input grade")
dim objXL
set objXl = WScript.CreateObject("Excel.Application")

objXL.workbooks.open("C:\Documents and Settings\Administrator.HOME\My Documents\digital marking\Digital Markbook 2004.xls")

objXL.visible = True
x = objxl.sheets.count
for i = 1 to x
objxl.sheets(i).activate
next


if cint(idnumber) = objxl.cells(i,1).value then
objxl.cells(i,2).value = grade
done = idnumber & " Grade Inserted"
end if
if not(objxl.cells(i,1) > " ") then
done = idnumber & " Not Found"
end if


msgbox(done)


Still will not put in grade so what am I doing wrong it says
done?
Thanks J F


Some lead, some follow....I just Hope!

 

[PHV]

You may try something like this:
objXL.Visible = True
done = idnumber & " Not Found"
For s = 1 To objXL.Sheets.Count
objXL.Sheets(s).Activate
i = 1
Do
If CInt(idnumber) = objXL.Cells(i,1).Value Then
objXL.Cells(i,2).Value = grade
done = idnumber & " Grade Inserted in sheet " & s
Exit For
End If
i = i + 1
If Trim(objXL.Cells(i,1)) = "" Then Exit Do
Loop
Next
MsgBox done

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[itsfisko]

PHV
Thanks for the tip I just need to find out where it is pasting the "Grade" entry as I cannot figure out where it is pasing it into the work book
Thanks JF

Some lead, some follow....I just Hope!

 

[infinitelo]

Thanks phv that looks right.

itsfisko, if the id is in column A then the grade should
be in column B.

looks like you want more than one grade per id number.
-----------------------------------------------------

objXL.Visible = True
done = idnumber & " Not Found"
For s = 1 To objXL.Sheets.Count
objXL.Sheets(s).Activate
i = 1
Do
If CInt(idnumber) = objXL.Cells(i,1).Value Then
'8 is column h, this will loop until empty cell
'on row with idnumber (id is A right???)
j = 8
do while objxl.cells(i,j).value > " "
j = j+1
loop

objXL.Cells(i,j).Value = grade
done = idnumber & " Grade Inserted in sheet " & s
Exit For
End If
i = i + 1
If Trim(objXL.Cells(i,1)) = "" Then Exit Do
Loop
Next
MsgBox done

if it is to be it's up to me

 

[itsfisko]

Hi this sort of works however it only looks at row 1 column A
it won't look or find the Id number if is any where else
Code:-


idnumber = inputbox("input id number")
grade = inputbox("input grade")
dim objXL
set objXl = WScript.CreateObject("Excel.Application")

objXL.workbooks.open("C:\Documents and Settings\Administrator.HOME\My Documents\digital marking\Digital Markbook 2004.xls")






objXL.Visible = True
done = idnumber & " Not Found"
For s = 1 To objXL.Sheets.Count
objXL.Sheets(s).Activate
i = 1
Do
If CInt(idnumber) = objXL.Cells(i,1).Value Then
'8 is column h, this will loop until empty cell
'on row with idnumber (id is A right???)
j = 8
do while objxl.cells(i,j).value > " "
j = j+1
loop

objXL.Cells(i,j).Value = grade
done = idnumber & " Grade Inserted in sheet " & s
Exit For
End If
i = i + 1
If Trim(objXL.Cells(i,1)) = "" Then Exit Do
Loop
Next
MsgBox done




any ideas?
thanks again John











Some lead, some follow....I just Hope!

 

[itsfisko]

I have found the (one ) of the problems.
My excel sheet has the id numbers starting from row 11...
unfortunatly this has to be where the id numbers start from, I tried changing i = 1 to i = 11 this did not work.
Also as you mentioned I will need this procedure to add more than one grade to each id number and it will need to add grades to more than one id number.
It also needs a way of closing the procedure and saving the workbook (which might as well be open all the time and the grades added until all the id numbers have been marked)
I am begining to think that a form in the workbook may be the best way to do this.
Can some of the code be placed into the form/
or can what you have done so far achieve the same result.
Thanks guys, John Stressed school tech ;-)

Some lead, some follow....I just Hope!

 

[infinitelo]

you can copy some of the code from vbscript to vba(excel form)

don't know why changeing i = 1 to i = 11 didnt work,

the current code should add more than one grade per id,
you will need another do loop for more than one id number
''''''''''''''''''''''''''
moreinfo = "yes"
do while moreinfo = "yes"

paste rest of code here

moreinfo = inputbox("add more info?")
loop
objxl.quit
''''''''''''''''''''''''''

the objxl.quit will propt the user to to save and
exit excel



if it is to be it's up to me

 

[itsfisko]

Thanks once again infinitelo, I will try out your suggestions
John slightly less stressed school tech ( its half term!!)

Some lead, some follow....I just Hope!

 

[itsfisko]

Hi its very close to the answer now, However it still fails to find the id numbers in column A of the sheets in the workbook and if I place the id numbers in Column A row 1 then it wants to do them in sequence, Ie row 1 then row 2 etc rather than actually looking through all the rows (A) in each sheet to find a match. Am I expecting too much here? I would like to be able to input any id number and get it to match the id number and then insert the mark.
I have experimented with an input box ie Input subject (this would change the value j) and therefore change the column that the mark is placed on.
Any ideas you have both been ace!!
Thanks John School tech burning the midnight oil |-I

Some lead, some follow....I just Hope!

 

[PHV]

???
it wants to do them in sequence, Ie row 1 then row 2 etc
versus
looking through all the rows (A)
I don't see the difference

 

[itsfisko]

Hi PHV the problem is my data (id numbers0 start at row 11 in each sheet of the workbook. If i enter a number that is in row 12 of a sheet it fails to find it, unless I have already entered the number that was in row 11... I assumed that the code would just trawl thru the work book looking at column A until it found the id number that had been asked for. It flicks from sheet to sheet so I assumed it was looking thru them.
Thanks for you quick reply
John

Some lead, some follow....I just Hope!