Auto-Replacing Blank Fields

[Guest]

Hello.

I have an Excel file with around 15,000 records. Most are multiple test
grades for one student.

My question is, how can I use VBA to automatically go through each field,
find the name of a unique student (keep in mind that some students have the
same first and/or last name) and copy their name all the way down to their
last test grade and start the process over again for the next unique student
name. I would send the file so you could look at it but it is information
that should be kept private.

Here is an example of what the cells look like (each piece of text/number is
in it's own cell):
LName FName MName Test1 6/3/2003 42
Test2 6/3/2003 49
Test3 6/3/2003 41
Test4 6/3/2003 37

I would like to be able to automatically (using VBA) fill in the blank
cells, as in, fill in LName, FName and MName all the way down to the last
test, Test4 then go down to the next student and start the process over again.

Is there any example code I could look at?

Thanks you guys!

 

[KRCowen]

If you want to do what I think you want to do, you can select the column that
you want to populate and run the following code. You can either run it for
each column or modify it to handle multiple columns.

Sub test()

For i = 1 To Selection.Rows.Count
If Cells(ActiveCell.Row, ActiveCell.Column).Offset(1, 0) = "" Then
ActiveCell.Offset(1, 0).Value = ActiveCell.Value
ActiveCell.Offset(1, 0).Activate
Else
ActiveCell.Offset(1, 0).Activate
End If
Next i

End Sub

I have assumed that all the test scores for a student are in consecutive rows.
If that is not the case then I am not sure how you can tell what scores go with
what student. If a student appears with several blocks of rows, you can run
this code, then sort by the name to get all of a students stuff together.

I hope this helps.

Ken
Norfolk, Va

 

[Gord Dibben]

Note: you can do this without a macro.

Select the columns and F5>Special>Blanks>OK

Enter = sign in active cell then mouse-click on cell above and hit CRTL +
ENTER.

Copy then Paste Special>Values>OK>Esc.


Macro from Dave Peterson...........

Sub Fill_Blanks()
'by Dave Peterson 2004-01-06
'fill blank cells in column with value above
Dim wks As Worksheet
Dim Rng As Range
Dim LastRow As Long
Dim Col As Long

Set wks = ActiveSheet
With wks
Col = ActiveCell.Column
'or
'col = .range("b1").column

Set Rng = .UsedRange 'try to reset the lastcell
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
Set Rng = Nothing
On Error Resume Next
Set Rng = .Range(.Cells(2, Col), .Cells(LastRow, Col)) _
.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If Rng Is Nothing Then
MsgBox "No blanks found"
Exit Sub
Else
Rng.FormulaR1C1 = "=R[-1]C"
End If

'replace formulas with values
With .Cells(1, Col).EntireColumn
.Value = .Value
End With

End With

End Sub


Gord Dibben Excel MVP