URGENT HELP NEEDED!!! AUTOMATING PICKING OUT ROWS THAT HAVE SPECIFIC CELLS MATCHING A VALUE AND COPY
Posted by DANIEL CREMIN on February 17, 2001 5:53 AM
My problem is that i need to build a marks and grades system that will allow for students who outpreform on at least 2 of the 6 test to be displayed on another worksheet. The way i have deisgned the marks and grades system is one where each student gets a predicted level (scorelike 4.5 or 5.6. They then take tests where they get marks which are automatically worked out as percentages which are then looked up against a list of Level scorces so that a level for that test can be worked out. I have made a series of cells along each row that work out the differnece between the Level score for the test and the Predicted Level score. I need a macro or some form of automated procedure that will somehow copy entire rows, that have entries in at least 2 of the Level Score varience fields which are greater than 1.0, into another worksheet.
Posted by David Hawley on February 17, 2001 10:56 PM
Hi Daniel
There are many ways to do this, but by far the quickest would be to use the AutoFilter via VBA.
The code below will filter a table in Range A1:Fwhatever on a called "Sheet1" to fit the criteria of greater 1.0 in Columns "C" & "D" (Field 3 & 4). Then copy the filtered range to A1 of a sheet called "Over1"
Sub CopyOver()
With Sheets("Sheet1")
.AutoFilterMode = False
.Range("A1:F1").AutoFilter
.Range("A1:F1").AutoFilter Field:=3, Criteria1:=">=1"
.Range("A1:F1").AutoFilter Field:=4, Criteria1:=">=1"
.UsedRange.SpecialCells(xlCellTypeVisible).Copy _
Destination:=Sheets("Over1").Range("A1")
.AutoFilterMode = False
End With
End Sub
Dave
OzGrid Business Applications
Posted by Daniel Cremin on February 18, 2001 8:07 AM
MORE HELP PLEASE DAVE!
First thanks for responding so quickly. I still dont understand how i can get excel to select entire rows and copy them into another worksheet where any 2 or more of the six test level variences are greater than 1.0. How will excel be able to handle multiple rows where this happens (More than one student over performing at least twice. Thanks again.
Posted by Dave Hawley on February 18, 2001 3:01 PM
Re: MORE HELP PLEASE DAVE!
Daniel, I didn't see the entie row bit, but no problems. Change ".UsedRange.SpecialCells(xlCellTypeVisible.Copy" To
.UsedRange.SpecialCells(xlCellTypeVisible).entirerow.Copy
To see what my code does: on a sheet called Sheet1 set up some headings in row 1 Column A:F. Insert a new sheet and call it "Over1". Below your headings in Columns C & D put some numbers, some over 1 and some below. Then run the code.
Dave
- OzGrid Business Applications
Posted by David Hawley on February 19, 2001 2:51 AM
Re: MORE HELP PLEASE DAVE!
Daniel, have you tried the code from my first post ? or my second ?
It will handle the multiple entire rows because the code will only select the visible cells and those visible cells are all greater than 1.0
Filter a list using Excels Auto filter then select any cell in your list and push F5, then click Special-Visible cells. Copy these to another sheet and you will see.
Dave
OzGrid Business Applications
