Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto-Replacing Blank Fields
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto-Replacing Blank Fields
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto-Replacing Blank Fields
Note: you can do this without a macro.
Select the columns and F5SpecialBlanksOK Enter = sign in active cell then mouse-click on cell above and hit CRTL + ENTER. Copy then Paste SpecialValuesOKEsc. 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 On Wed, 1 Dec 2004 16:35:09 -0800, "TC10284" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Replacing blank cells with zeros | Excel Discussion (Misc queries) | |||
Excel SHOULD NOT AUTO-CHANGE formated text fields to DATE FIELDS! | Excel Worksheet Functions | |||
Replacing blank cells | Excel Worksheet Functions | |||
Replacing 0 with blank | Excel Programming | |||
replacing 0 with blank | Excel Programming |