DOu Until or something else? Heres a bit of a challenge

Posted by Daniel Cremin on December 11, 2001 8:44 AM

Hi i need help with a feature of a Marks and Grades
System that im currently building. A really quick intro
is needed to understand the prob. I have a main sheet
called Processing where student details and marks,
percents, grades, etc are stored. I dont want the user
to see all this stuff but they need to be able to see
a summary of grades etc (this i have down) but i also
want them to be able to use another sheet to access
certain student details (name, class and the test marks)
) this is also now done, but the Problem is that i want
there to be an update system macro that will copy the
values entered in the cells on the student editor sheet
and one by one paste the values in the appropriate cell
of the same student on the processing sheet. I can do
all the offsetting and pasting bit myself i just need a
neat way (a Do Until statement or something like it i
bet) to be able to get excel to find the same student
on the processing sheet (one with a matching name in the
row or in column B)so i can do the rest.

Thanks very much in advance.

Posted by CMorrigu on December 11, 2001 11:31 AM

this goes along with the code I queried about and finally posted sometime in the past month or so.. which has probably fallen off the board now.

Here's an example...

Do While srcline &LT;= LastRow ' source line &LT; eof
srcline = srcline + 1 ' inc src line
Windows(pivfile).Activate ' set source file
Cells(srcline, 1).Select ' select name to find
If Selection.Text = "" Then Exit Do ' exit loop if cell is blank
Label = ActiveCell.Value ' get first search value
Call OneOffs(Label)
Windows(repfile).Activate ' switch to source file
With Worksheets(ReportSheet).Range(Cells(1, 2), Cells(500, 2))
Set SearchLabel = .Find(Label, LookIn:=xlValues, LookAt:=xlPart) ' search for Label
If Not SearchLabel Is Nothing Then
aline = SearchLabel.Row ' set aline to found row
Windows(pivfile).Activate ' switch to source file
Range("B" + Right(Str(srcline), Len(Str(srcline)) - 1)).Select ' select value
If TestLength = 0 Then ActiveCell.Value = ActiveCell.Value * 4
Selection.Copy ' copy value
Windows(repfile).Activate ' switch to report file
Cells(aline, LastCol).Select ' set paste cell
ActiveSheet.Paste ' paste value
End If
End With
ShadowSource

Posted by Rick E on December 11, 2001 12:13 PM

Do Until Code

Here is some code to step down a column in search of a certain value.

' first put the value (Student name) in a string
' assumes you have selected the cell with the name
' now start macro
Sname = ActiveCell.Value
' now select the sheet to search
Sheets("Processing").Select
' select the column to search
Range("B1").Select ' start at B2 or at row other?
' this assumes there is a header for columns
' now start looking for a match with a loop
k = 0 ' set a value to stop looking
j = 0 ' set a value for max cells to look at
Do Until k = 1
ActiveCell.Offset(1,0).Select ' move down one cell
j = j + 1
if ActiveCell.Value = Sname Then
k = 1 ' Now on the same name cell
EndIf
' if after 500 cells no match, stop looking
If j = 500 then ' could be any number here
MsgBox "No match found"
Exit Do ' Or Exit Sub or something else
EndIf
Loop
If j = 500 Then Exit Sub ' should be the same
' value as the other If above
' If at this point, active cell has the same name
' Good luck
' add your copy/paste code below...