Do Until statement for finding first empty cell (gets harder)

Posted by Daniel C on November 25, 2001 12:31 AM

Hi and thanks to any Excel experts who can help me out.
Im designing a marks and grades system and have only one
last hurdle left to cross. I need to be able to have a
Do Until Statement that searches the list of student
names until it finds the first blank cell (starting
from cell C7 which is a title cell saying student), and
then proceed to paste in the student name from a named
range ("studentname") on another sheet. It then has
to offset one to the left and paste in the teacher name
from a named range called teacger and
then it needs to go two to the right
and paste in Predicted level (next to student name).

Thanks to anyone who can help me

Posted by Tom Dickinson on November 25, 2001 8:26 PM

Sounds easy enough (or am I missing something?)
Set up a counter to give you the row that is blank.
Dim RwCtr as integer
RwCtr = 8
Do while range("C" & RwCtr) &LT;&GT; Empty
RwCtr = RwCtr + 1
Loop
Range("C" & RwCtr) = Range(studentname)
' or do you need to offset within the stuentname range?
Range("C" & RwCtr).Offset(, -1) = Range(TeacherName)
Range("C" & RwCtr).Offset(, 1) = PredictedLevel
'Don't know what is meant by predicted level.
End Sub

Hope this helps

Posted by Bib on November 26, 2001 4:58 AM

Maybe you don't need a macro to do that. Have you tried the VLOOKUP function ?

To find the last empty cell, no need to count them all, just use the Range.CurrentRegion property that gives you the border cells of the area you're in.