Mcygee
03-27-2009, 02:51 PM
Hello everybody!
I've been working hard on a code to automatically sort some data. I'm almost finished! However, I ran into a hopefully simple problem someone can resolve. Here is some code that I modified. The original code is thanks to XLD from another post. I just made it a loop and copy to new rows each time.
----------------------------------------------------
Sub seperate()
'----Stop Excel from Refreshing the Screen Until Macro is Done----
Application.ScreenUpdating = False
RowAdd = 2
Do
Sheets("Sheet1").Select
Columns("G:G").Select
NumPro = "Teacher3"
Set cell = Cells.Find(What:=NumPro, _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If Not cell Is Nothing Then
ChangingA = "A" & RowAdd
ChangingJ = "J" & RowAdd
'adjust these cells accordingly
Worksheets("Sheet2").Range(ChangingA, ChangingJ).Value = Range(Cells(cell.Row, "A"), Cells(cell.Row, "J")).Value
cell.EntireRow.Delete
RowAdd = RowAdd + 1
End If
Loop Until (cell Is Nothing)
End Sub
---------------------------------------------------
This is doing almost exactly what I want it to do, except for one small thing. In order to complete most of the code I just had it search for "Teacher3" for test purposes. But I need to change it.
I need it to find numbers greater than a specific number instead of text. Specifically, any time greater than 2:30.
So I'm wondering if there is a way to make this part of the code....
NumPro = "Teacher3"
Set cell = Cells.Find(What:=NumPro, _
Request any time greater than 2:30 P.M. or 14:30, instead of searching for the string "Teacher3". I'm sure if I can at least make it just search for a number greater than X I can figure out the time part on my own. Either way I could use the help.
ANY HELP IS MUCH APPRECIATED!
I've been working hard on a code to automatically sort some data. I'm almost finished! However, I ran into a hopefully simple problem someone can resolve. Here is some code that I modified. The original code is thanks to XLD from another post. I just made it a loop and copy to new rows each time.
----------------------------------------------------
Sub seperate()
'----Stop Excel from Refreshing the Screen Until Macro is Done----
Application.ScreenUpdating = False
RowAdd = 2
Do
Sheets("Sheet1").Select
Columns("G:G").Select
NumPro = "Teacher3"
Set cell = Cells.Find(What:=NumPro, _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If Not cell Is Nothing Then
ChangingA = "A" & RowAdd
ChangingJ = "J" & RowAdd
'adjust these cells accordingly
Worksheets("Sheet2").Range(ChangingA, ChangingJ).Value = Range(Cells(cell.Row, "A"), Cells(cell.Row, "J")).Value
cell.EntireRow.Delete
RowAdd = RowAdd + 1
End If
Loop Until (cell Is Nothing)
End Sub
---------------------------------------------------
This is doing almost exactly what I want it to do, except for one small thing. In order to complete most of the code I just had it search for "Teacher3" for test purposes. But I need to change it.
I need it to find numbers greater than a specific number instead of text. Specifically, any time greater than 2:30.
So I'm wondering if there is a way to make this part of the code....
NumPro = "Teacher3"
Set cell = Cells.Find(What:=NumPro, _
Request any time greater than 2:30 P.M. or 14:30, instead of searching for the string "Teacher3". I'm sure if I can at least make it just search for a number greater than X I can figure out the time part on my own. Either way I could use the help.
ANY HELP IS MUCH APPRECIATED!