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!

 

[PHV]

So the id numbers start at row 11 in every sheet and there is no empty cell in column A from row 11 until the end and all id numbers are unique in the workbook ?
Try this code to see how many rows are searched per sheet:

objXL.Visible = True
done = idnumber & " Not Found"
For s = 1 To objXL.Sheets.Count
  objXL.Sheets(s).Activate
  i = 11
  Do
    If objXL.Cells(i,1).Value = idnumber Then
      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
      MsgBox idnumber & " not found in A11:A" & i
      Exit Do
    End If
  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]

Hi I have just run the script and it returned a not found for all the sheets when the id number I entered was on the sheets.
However if I put the number in column A row I it finds it...
but it will not find the number below it unless I input the top number first
Thanks for your help.John ;-)

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

 

[PHV]

in column A row I
??? the script is supposed to start the search at row 11 !
What are the range displayed with the not found messages on each sheet ?

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

 

[itsfisko]

The range is a:11 a:12
this is on all sheets, Does it matter if the sheet is filtered with frozen panes?
Cheers John

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

 

[PHV]

And this ?

objXL.Visible = True
done = idnumber & " Not Found"
For s = 1 To objXL.Sheets.Count
  objXL.Sheets(s).Activate
  i = 11
  r = objXL.Cells(65536,1).End(&HFFFFEFBE).Row 'xlUp
  Do
    If objXL.Cells(i,1).Value = idnumber Then
      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 i > r Then
      MsgBox idnumber & " not found in A11:A" & i
      Exit Do
    End If
  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]

Hi PHV
thanks again the message is still not found A11 :150 (end of data in each sheet) it seems to look at the sheets but unless it has data in row 1 then it won't read it.
I think I will have to redesign the data sheets to allow my data to start at row 1 column 1 on each page.
Thanks John, Bemused school tech..

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

 

[itsfisko]

Thanks for all your help, however it still baffles me so I will put the idea on hold for a while,
It is something I will have to come back to as I have a new admin network (server 2003 Active Directory) to set up this week
I am still interested in any thoughts on Scripting or forms in Excel
JF back @school tech