| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro criteria copy
Hi, i have a dabase like this...
A B C D 11/01 7:23:30 in ADRIAN 11/01 16:05:29 out ADRIAN 11/01 17:15:02 out ADRIAN 11/01 7:23:44 IN ALEC 11/01 16:04:34 out ALEC 11/03 12:17:22 IN ALEC 11/03 21:10:30 out ALEC ..... ....and i need a macro to extract datas in another sheet, like this: G H I J data pers IN out 11/01 ADRIAN 7:23:30 16:05:29 11/01 ADRIAN 17:15:02 11/01 ALEC 7:23:44 16:04:34 11/03 ALEC 12:17:22 21:10:30 I need everything to be sorted by name and date, ascending. Can this be done? Thanks in advance. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
macro criteria copy
Public Sub ProcessData()
Dim sh As Worksheet Dim i As Long Dim LastRow As Long Dim NextRow As Long Set sh = Worksheets("Sheet2") sh.Range("G1:J1").Value = Array("data", "pers", "IN", "out") NextRow = 2 With ActiveSheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For i = 1 To LastRow If .Cells(i, "D").Value = .Cells(i + 1, "D").Value Then If LCase(.Cells(i, "C").Value) = "in" And _ LCase(.Cells(i + 1, "C").Value) = "out" Then sh.Cells(NextRow, "G").Value = .Cells(i, "A").Value sh.Cells(NextRow, "H").Value = .Cells(i, "D").Value sh.Cells(NextRow, "I").Value = .Cells(i, "B").Value sh.Cells(NextRow, "J").Value = .Cells(i + 1, "B").Value i = i + 1 Else sh.Cells(i, "G").Value = .Cells(i, "A").Value sh.Cells(NextRow, "H").Value = .Cells(i, "D").Value If LCase(.Cells(i, "C").Value) = "in" Then sh.Cells(NextRow, "I").Value = .Cells(i, "B").Value Else sh.Cells(NextRow, "J").Value = .Cells(i, "B").Value End If End If Else sh.Cells(i, "G").Value = .Cells(i, "A").Value sh.Cells(NextRow, "H").Value = .Cells(i, "D").Value If LCase(.Cells(i, "C").Value) = "in" Then sh.Cells(NextRow, "I").Value = .Cells(i, "B").Value Else sh.Cells(NextRow, "J").Value = .Cells(i, "B").Value End If End If NextRow = NextRow + 1 Next i End With End Sub -- __________________________________ HTH Bob "puiuluipui" wrote in message ... Hi, i have a dabase like this... A B C D 11/01 7:23:30 in ADRIAN 11/01 16:05:29 out ADRIAN 11/01 17:15:02 out ADRIAN 11/01 7:23:44 IN ALEC 11/01 16:04:34 out ALEC 11/03 12:17:22 IN ALEC 11/03 21:10:30 out ALEC ..... ...and i need a macro to extract datas in another sheet, like this: G H I J data pers IN out 11/01 ADRIAN 7:23:30 16:05:29 11/01 ADRIAN 17:15:02 11/01 ALEC 7:23:44 16:04:34 11/03 ALEC 12:17:22 21:10:30 I need everything to be sorted by name and date, ascending. Can this be done? Thanks in advance. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
macro criteria copy
Hi, it's not working. It doesn't show me anything.
What am i doing wrong? "Bob Phillips" wrote: Public Sub ProcessData() Dim sh As Worksheet Dim i As Long Dim LastRow As Long Dim NextRow As Long Set sh = Worksheets("Sheet2") sh.Range("G1:J1").Value = Array("data", "pers", "IN", "out") NextRow = 2 With ActiveSheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For i = 1 To LastRow If .Cells(i, "D").Value = .Cells(i + 1, "D").Value Then If LCase(.Cells(i, "C").Value) = "in" And _ LCase(.Cells(i + 1, "C").Value) = "out" Then sh.Cells(NextRow, "G").Value = .Cells(i, "A").Value sh.Cells(NextRow, "H").Value = .Cells(i, "D").Value sh.Cells(NextRow, "I").Value = .Cells(i, "B").Value sh.Cells(NextRow, "J").Value = .Cells(i + 1, "B").Value i = i + 1 Else sh.Cells(i, "G").Value = .Cells(i, "A").Value sh.Cells(NextRow, "H").Value = .Cells(i, "D").Value If LCase(.Cells(i, "C").Value) = "in" Then sh.Cells(NextRow, "I").Value = .Cells(i, "B").Value Else sh.Cells(NextRow, "J").Value = .Cells(i, "B").Value End If End If Else sh.Cells(i, "G").Value = .Cells(i, "A").Value sh.Cells(NextRow, "H").Value = .Cells(i, "D").Value If LCase(.Cells(i, "C").Value) = "in" Then sh.Cells(NextRow, "I").Value = .Cells(i, "B").Value Else sh.Cells(NextRow, "J").Value = .Cells(i, "B").Value End If End If NextRow = NextRow + 1 Next i End With End Sub -- __________________________________ HTH Bob "puiuluipui" wrote in message ... Hi, i have a dabase like this... A B C D 11/01 7:23:30 in ADRIAN 11/01 16:05:29 out ADRIAN 11/01 17:15:02 out ADRIAN 11/01 7:23:44 IN ALEC 11/01 16:04:34 out ALEC 11/03 12:17:22 IN ALEC 11/03 21:10:30 out ALEC ..... ...and i need a macro to extract datas in another sheet, like this: G H I J data pers IN out 11/01 ADRIAN 7:23:30 16:05:29 11/01 ADRIAN 17:15:02 11/01 ALEC 7:23:44 16:04:34 11/03 ALEC 12:17:22 21:10:30 I need everything to be sorted by name and date, ascending. Can this be done? Thanks in advance. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
macro criteria copy
Well it worked for me, so what did you do with that code.
-- __________________________________ HTH Bob "puiuluipui" wrote in message ... Hi, it's not working. It doesn't show me anything. What am i doing wrong? "Bob Phillips" wrote: Public Sub ProcessData() Dim sh As Worksheet Dim i As Long Dim LastRow As Long Dim NextRow As Long Set sh = Worksheets("Sheet2") sh.Range("G1:J1").Value = Array("data", "pers", "IN", "out") NextRow = 2 With ActiveSheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For i = 1 To LastRow If .Cells(i, "D").Value = .Cells(i + 1, "D").Value Then If LCase(.Cells(i, "C").Value) = "in" And _ LCase(.Cells(i + 1, "C").Value) = "out" Then sh.Cells(NextRow, "G").Value = .Cells(i, "A").Value sh.Cells(NextRow, "H").Value = .Cells(i, "D").Value sh.Cells(NextRow, "I").Value = .Cells(i, "B").Value sh.Cells(NextRow, "J").Value = .Cells(i + 1, "B").Value i = i + 1 Else sh.Cells(i, "G").Value = .Cells(i, "A").Value sh.Cells(NextRow, "H").Value = .Cells(i, "D").Value If LCase(.Cells(i, "C").Value) = "in" Then sh.Cells(NextRow, "I").Value = .Cells(i, "B").Value Else sh.Cells(NextRow, "J").Value = .Cells(i, "B").Value End If End If Else sh.Cells(i, "G").Value = .Cells(i, "A").Value sh.Cells(NextRow, "H").Value = .Cells(i, "D").Value If LCase(.Cells(i, "C").Value) = "in" Then sh.Cells(NextRow, "I").Value = .Cells(i, "B").Value Else sh.Cells(NextRow, "J").Value = .Cells(i, "B").Value End If End If NextRow = NextRow + 1 Next i End With End Sub -- __________________________________ HTH Bob "puiuluipui" wrote in message ... Hi, i have a dabase like this... A B C D 11/01 7:23:30 in ADRIAN 11/01 16:05:29 out ADRIAN 11/01 17:15:02 out ADRIAN 11/01 7:23:44 IN ALEC 11/01 16:04:34 out ALEC 11/03 12:17:22 IN ALEC 11/03 21:10:30 out ALEC ..... ...and i need a macro to extract datas in another sheet, like this: G H I J data pers IN out 11/01 ADRIAN 7:23:30 16:05:29 11/01 ADRIAN 17:15:02 11/01 ALEC 7:23:44 16:04:34 11/03 ALEC 12:17:22 21:10:30 I need everything to be sorted by name and date, ascending. Can this be done? Thanks in advance. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
macro criteria copy
Put it in the sheet, not in a code module, maybe?
SD "Bob Phillips" wrote: Well it worked for me, so what did you do with that code. -- __________________________________ HTH Bob "puiuluipui" wrote in message ... Hi, it's not working. It doesn't show me anything. What am i doing wrong? "Bob Phillips" wrote: Public Sub ProcessData() Dim sh As Worksheet Dim i As Long Dim LastRow As Long Dim NextRow As Long Set sh = Worksheets("Sheet2") sh.Range("G1:J1").Value = Array("data", "pers", "IN", "out") NextRow = 2 With ActiveSheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For i = 1 To LastRow If .Cells(i, "D").Value = .Cells(i + 1, "D").Value Then If LCase(.Cells(i, "C").Value) = "in" And _ LCase(.Cells(i + 1, "C").Value) = "out" Then sh.Cells(NextRow, "G").Value = .Cells(i, "A").Value sh.Cells(NextRow, "H").Value = .Cells(i, "D").Value sh.Cells(NextRow, "I").Value = .Cells(i, "B").Value sh.Cells(NextRow, "J").Value = .Cells(i + 1, "B").Value i = i + 1 Else sh.Cells(i, "G").Value = .Cells(i, "A").Value sh.Cells(NextRow, "H").Value = .Cells(i, "D").Value If LCase(.Cells(i, "C").Value) = "in" Then sh.Cells(NextRow, "I").Value = .Cells(i, "B").Value Else sh.Cells(NextRow, "J").Value = .Cells(i, "B").Value End If End If Else sh.Cells(i, "G").Value = .Cells(i, "A").Value sh.Cells(NextRow, "H").Value = .Cells(i, "D").Value If LCase(.Cells(i, "C").Value) = "in" Then sh.Cells(NextRow, "I").Value = .Cells(i, "B").Value Else sh.Cells(NextRow, "J").Value = .Cells(i, "B").Value End If End If NextRow = NextRow + 1 Next i End With End Sub -- __________________________________ HTH Bob "puiuluipui" wrote in message ... Hi, i have a dabase like this... A B C D 11/01 7:23:30 in ADRIAN 11/01 16:05:29 out ADRIAN 11/01 17:15:02 out ADRIAN 11/01 7:23:44 IN ALEC 11/01 16:04:34 out ALEC 11/03 12:17:22 IN ALEC 11/03 21:10:30 out ALEC ..... ...and i need a macro to extract datas in another sheet, like this: G H I J data pers IN out 11/01 ADRIAN 7:23:30 16:05:29 11/01 ADRIAN 17:15:02 11/01 ALEC 7:23:44 16:04:34 11/03 ALEC 12:17:22 21:10:30 I need everything to be sorted by name and date, ascending. Can this be done? Thanks in advance. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
macro criteria copy
Hi, on sheet 2 tabright clickview codethan paste your code.
I did the same in sheet 1 tab, but nothing. What am i doing wrong? Maybe the country region? I mean "comma" and other signs? "," with ";" ? I dont know. Maybe it's something even more simple. Hope you can figure this out. Thaks in advance. "Bob Phillips" wrote: Well it worked for me, so what did you do with that code. -- __________________________________ HTH Bob "puiuluipui" wrote in message ... Hi, it's not working. It doesn't show me anything. What am i doing wrong? "Bob Phillips" wrote: Public Sub ProcessData() Dim sh As Worksheet Dim i As Long Dim LastRow As Long Dim NextRow As Long Set sh = Worksheets("Sheet2") sh.Range("G1:J1").Value = Array("data", "pers", "IN", "out") NextRow = 2 With ActiveSheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For i = 1 To LastRow If .Cells(i, "D").Value = .Cells(i + 1, "D").Value Then If LCase(.Cells(i, "C").Value) = "in" And _ LCase(.Cells(i + 1, "C").Value) = "out" Then sh.Cells(NextRow, "G").Value = .Cells(i, "A").Value sh.Cells(NextRow, "H").Value = .Cells(i, "D").Value sh.Cells(NextRow, "I").Value = .Cells(i, "B").Value sh.Cells(NextRow, "J").Value = .Cells(i + 1, "B").Value i = i + 1 Else sh.Cells(i, "G").Value = .Cells(i, "A").Value sh.Cells(NextRow, "H").Value = .Cells(i, "D").Value If LCase(.Cells(i, "C").Value) = "in" Then sh.Cells(NextRow, "I").Value = .Cells(i, "B").Value Else sh.Cells(NextRow, "J").Value = .Cells(i, "B").Value End If End If Else sh.Cells(i, "G").Value = .Cells(i, "A").Value sh.Cells(NextRow, "H").Value = .Cells(i, "D").Value If LCase(.Cells(i, "C").Value) = "in" Then sh.Cells(NextRow, "I").Value = .Cells(i, "B").Value Else sh.Cells(NextRow, "J").Value = .Cells(i, "B").Value End If End If NextRow = NextRow + 1 Next i End With End Sub -- __________________________________ HTH Bob "puiuluipui" wrote in message ... Hi, i have a dabase like this... A B C D 11/01 7:23:30 in ADRIAN 11/01 16:05:29 out ADRIAN 11/01 17:15:02 out ADRIAN 11/01 7:23:44 IN ALEC 11/01 16:04:34 out ALEC 11/03 12:17:22 IN ALEC 11/03 21:10:30 out ALEC ..... ...and i need a macro to extract datas in another sheet, like this: G H I J data pers IN out 11/01 ADRIAN 7:23:30 16:05:29 11/01 ADRIAN 17:15:02 11/01 ALEC 7:23:44 16:04:34 11/03 ALEC 12:17:22 21:10:30 I need everything to be sorted by name and date, ascending. Can this be done? Thanks in advance. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
macro criteria copy
Hi, i put it in sheet 2 tabright clickview codethan paste the code.
it's not right? "Shane Devenshire" wrote: Put it in the sheet, not in a code module, maybe? SD "Bob Phillips" wrote: Well it worked for me, so what did you do with that code. -- __________________________________ HTH Bob "puiuluipui" wrote in message ... Hi, it's not working. It doesn't show me anything. What am i doing wrong? "Bob Phillips" wrote: Public Sub ProcessData() Dim sh As Worksheet Dim i As Long Dim LastRow As Long Dim NextRow As Long Set sh = Worksheets("Sheet2") sh.Range("G1:J1").Value = Array("data", "pers", "IN", "out") NextRow = 2 With ActiveSheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For i = 1 To LastRow If .Cells(i, "D").Value = .Cells(i + 1, "D").Value Then If LCase(.Cells(i, "C").Value) = "in" And _ LCase(.Cells(i + 1, "C").Value) = "out" Then sh.Cells(NextRow, "G").Value = .Cells(i, "A").Value sh.Cells(NextRow, "H").Value = .Cells(i, "D").Value sh.Cells(NextRow, "I").Value = .Cells(i, "B").Value sh.Cells(NextRow, "J").Value = .Cells(i + 1, "B").Value i = i + 1 Else sh.Cells(i, "G").Value = .Cells(i, "A").Value sh.Cells(NextRow, "H").Value = .Cells(i, "D").Value If LCase(.Cells(i, "C").Value) = "in" Then sh.Cells(NextRow, "I").Value = .Cells(i, "B").Value Else sh.Cells(NextRow, "J").Value = .Cells(i, "B").Value End If End If Else sh.Cells(i, "G").Value = .Cells(i, "A").Value sh.Cells(NextRow, "H").Value = .Cells(i, "D").Value If LCase(.Cells(i, "C").Value) = "in" Then sh.Cells(NextRow, "I").Value = .Cells(i, "B").Value Else sh.Cells(NextRow, "J").Value = .Cells(i, "B").Value End If End If NextRow = NextRow + 1 Next i End With End Sub -- __________________________________ HTH Bob "puiuluipui" wrote in message ... Hi, i have a dabase like this... A B C D 11/01 7:23:30 in ADRIAN 11/01 16:05:29 out ADRIAN 11/01 17:15:02 out ADRIAN 11/01 7:23:44 IN ALEC 11/01 16:04:34 out ALEC 11/03 12:17:22 IN ALEC 11/03 21:10:30 out ALEC ..... ...and i need a macro to extract datas in another sheet, like this: G H I J data pers IN out 11/01 ADRIAN 7:23:30 16:05:29 11/01 ADRIAN 17:15:02 11/01 ALEC 7:23:44 16:04:34 11/03 ALEC 12:17:22 21:10:30 I need everything to be sorted by name and date, ascending. Can this be done? Thanks in advance. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
macro criteria copy
Hi, on sheet 2 tabright clickview codethan paste your code.
I did the same in sheet 1 tab, but nothing. What am i doing wrong? Maybe the country region? I mean "comma" and other signs? "," with ";" ? I dont know. Maybe it's something even more simple. Hope you can figure this out. Thaks in advance. "Bob Phillips" wrote: Well it worked for me, so what did you do with that code. -- __________________________________ HTH Bob "puiuluipui" wrote in message ... Hi, it's not working. It doesn't show me anything. What am i doing wrong? "Bob Phillips" wrote: Public Sub ProcessData() Dim sh As Worksheet Dim i As Long Dim LastRow As Long Dim NextRow As Long Set sh = Worksheets("Sheet2") sh.Range("G1:J1").Value = Array("data", "pers", "IN", "out") NextRow = 2 With ActiveSheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For i = 1 To LastRow If .Cells(i, "D").Value = .Cells(i + 1, "D").Value Then If LCase(.Cells(i, "C").Value) = "in" And _ LCase(.Cells(i + 1, "C").Value) = "out" Then sh.Cells(NextRow, "G").Value = .Cells(i, "A").Value sh.Cells(NextRow, "H").Value = .Cells(i, "D").Value sh.Cells(NextRow, "I").Value = .Cells(i, "B").Value sh.Cells(NextRow, "J").Value = .Cells(i + 1, "B").Value i = i + 1 Else sh.Cells(i, "G").Value = .Cells(i, "A").Value sh.Cells(NextRow, "H").Value = .Cells(i, "D").Value If LCase(.Cells(i, "C").Value) = "in" Then sh.Cells(NextRow, "I").Value = .Cells(i, "B").Value Else sh.Cells(NextRow, "J").Value = .Cells(i, "B").Value End If End If Else sh.Cells(i, "G").Value = .Cells(i, "A").Value sh.Cells(NextRow, "H").Value = .Cells(i, "D").Value If LCase(.Cells(i, "C").Value) = "in" Then sh.Cells(NextRow, "I").Value = .Cells(i, "B").Value Else sh.Cells(NextRow, "J").Value = .Cells(i, "B").Value End If End If NextRow = NextRow + 1 Next i End With End Sub -- __________________________________ HTH Bob "puiuluipui" wrote in message ... Hi, i have a dabase like this... A B C D 11/01 7:23:30 in ADRIAN 11/01 16:05:29 out ADRIAN 11/01 17:15:02 out ADRIAN 11/01 7:23:44 IN ALEC 11/01 16:04:34 out ALEC 11/03 12:17:22 IN ALEC 11/03 21:10:30 out ALEC ..... ...and i need a macro to extract datas in another sheet, like this: G H I J data pers IN out 11/01 ADRIAN 7:23:30 16:05:29 11/01 ADRIAN 17:15:02 11/01 ALEC 7:23:44 16:04:34 11/03 ALEC 12:17:22 21:10:30 I need everything to be sorted by name and date, ascending. Can this be done? Thanks in advance. |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
macro criteria copy
No, put it in a standard code module (InsertModule), and just run it.
-- __________________________________ HTH Bob "puiuluipui" wrote in message ... Hi, on sheet 2 tabright clickview codethan paste your code. I did the same in sheet 1 tab, but nothing. What am i doing wrong? Maybe the country region? I mean "comma" and other signs? "," with ";" ? I dont know. Maybe it's something even more simple. Hope you can figure this out. Thaks in advance. "Bob Phillips" wrote: Well it worked for me, so what did you do with that code. -- __________________________________ HTH Bob "puiuluipui" wrote in message ... Hi, it's not working. It doesn't show me anything. What am i doing wrong? "Bob Phillips" wrote: Public Sub ProcessData() Dim sh As Worksheet Dim i As Long Dim LastRow As Long Dim NextRow As Long Set sh = Worksheets("Sheet2") sh.Range("G1:J1").Value = Array("data", "pers", "IN", "out") NextRow = 2 With ActiveSheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For i = 1 To LastRow If .Cells(i, "D").Value = .Cells(i + 1, "D").Value Then If LCase(.Cells(i, "C").Value) = "in" And _ LCase(.Cells(i + 1, "C").Value) = "out" Then sh.Cells(NextRow, "G").Value = .Cells(i, "A").Value sh.Cells(NextRow, "H").Value = .Cells(i, "D").Value sh.Cells(NextRow, "I").Value = .Cells(i, "B").Value sh.Cells(NextRow, "J").Value = .Cells(i + 1, "B").Value i = i + 1 Else sh.Cells(i, "G").Value = .Cells(i, "A").Value sh.Cells(NextRow, "H").Value = .Cells(i, "D").Value If LCase(.Cells(i, "C").Value) = "in" Then sh.Cells(NextRow, "I").Value = .Cells(i, "B").Value Else sh.Cells(NextRow, "J").Value = .Cells(i, "B").Value End If End If Else sh.Cells(i, "G").Value = .Cells(i, "A").Value sh.Cells(NextRow, "H").Value = .Cells(i, "D").Value If LCase(.Cells(i, "C").Value) = "in" Then sh.Cells(NextRow, "I").Value = .Cells(i, "B").Value Else sh.Cells(NextRow, "J").Value = .Cells(i, "B").Value End If End If NextRow = NextRow + 1 Next i End With End Sub -- __________________________________ HTH Bob "puiuluipui" wrote in message ... Hi, i have a dabase like this... A B C D 11/01 7:23:30 in ADRIAN 11/01 16:05:29 out ADRIAN 11/01 17:15:02 out ADRIAN 11/01 7:23:44 IN ALEC 11/01 16:04:34 out ALEC 11/03 12:17:22 IN ALEC 11/03 21:10:30 out ALEC ..... ...and i need a macro to extract datas in another sheet, like this: G H I J data pers IN out 11/01 ADRIAN 7:23:30 16:05:29 11/01 ADRIAN 17:15:02 11/01 ALEC 7:23:44 16:04:34 11/03 ALEC 12:17:22 21:10:30 I need everything to be sorted by name and date, ascending. Can this be done? Thanks in advance. |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
macro criteria copy
Hi, it's working. i had problems because i exported the data from a program
and some cells had space befor the text. But i have one problem. I have : A B C D 11/01 7:23:30 IN ADRIAN 11/01 16:05:29 out ADRIAN 11/01 17:15:02 out ADRIAN In the same date " 11/01" i have two rows with "out" and one row with "IN" After i run the macro, it doesn't shows the date. Something like that: sheet 2 (macro) 11/01 ADRIAN 7:23:30 16:05:29 ADRIAN 17:15:02 It doesn't matter if i have one "IN" and two "out" or one "out" and two "IN", in the date column, it doesn't show the date. I have with the same date, a lot of "IN" and a lot of "out". Not necessary the same number of "IN" and "out". If there is an empty cell in the "macro result sheet", it doesn't show the date. Is there a way to show the date even the cell it's blank? Even it has only one "IN" or one "out"? Thanks in advance. "Bob Phillips" a scris: No, put it in a standard code module (InsertModule), and just run it. -- __________________________________ HTH Bob "puiuluipui" wrote in message ... Hi, on sheet 2 tabright clickview codethan paste your code. I did the same in sheet 1 tab, but nothing. What am i doing wrong? Maybe the country region? I mean "comma" and other signs? "," with ";" ? I dont know. Maybe it's something even more simple. Hope you can figure this out. Thaks in advance. "Bob Phillips" wrote: Well it worked for me, so what did you do with that code. -- __________________________________ HTH Bob "puiuluipui" wrote in message ... Hi, it's not working. It doesn't show me anything. What am i doing wrong? "Bob Phillips" wrote: Public Sub ProcessData() Dim sh As Worksheet Dim i As Long Dim LastRow As Long Dim NextRow As Long Set sh = Worksheets("Sheet2") sh.Range("G1:J1").Value = Array("data", "pers", "IN", "out") NextRow = 2 With ActiveSheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For i = 1 To LastRow If .Cells(i, "D").Value = .Cells(i + 1, "D").Value Then If LCase(.Cells(i, "C").Value) = "in" And _ LCase(.Cells(i + 1, "C").Value) = "out" Then sh.Cells(NextRow, "G").Value = .Cells(i, "A").Value sh.Cells(NextRow, "H").Value = .Cells(i, "D").Value sh.Cells(NextRow, "I").Value = .Cells(i, "B").Value sh.Cells(NextRow, "J").Value = .Cells(i + 1, "B").Value i = i + 1 Else sh.Cells(i, "G").Value = .Cells(i, "A").Value sh.Cells(NextRow, "H").Value = .Cells(i, "D").Value If LCase(.Cells(i, "C").Value) = "in" Then sh.Cells(NextRow, "I").Value = .Cells(i, "B").Value Else sh.Cells(NextRow, "J").Value = .Cells(i, "B").Value End If End If Else sh.Cells(i, "G").Value = .Cells(i, "A").Value sh.Cells(NextRow, "H").Value = .Cells(i, "D").Value If LCase(.Cells(i, "C").Value) = "in" Then sh.Cells(NextRow, "I").Value = .Cells(i, "B").Value Else sh.Cells(NextRow, "J").Value = .Cells(i, "B").Value End If End If NextRow = NextRow + 1 Next i End With End Sub -- __________________________________ HTH Bob "puiuluipui" wrote in message ... Hi, i have a dabase like this... A B C D 11/01 7:23:30 in ADRIAN 11/01 16:05:29 out ADRIAN 11/01 17:15:02 out ADRIAN 11/01 7:23:44 IN ALEC 11/01 16:04:34 out ALEC 11/03 12:17:22 IN ALEC 11/03 21:10:30 out ALEC ..... ...and i need a macro to extract datas in another sheet, like this: G H I J data pers IN out 11/01 ADRIAN 7:23:30 16:05:29 11/01 ADRIAN 17:15:02 11/01 ALEC 7:23:44 16:04:34 11/03 ALEC 12:17:22 21:10:30 I need everything to be sorted by name and date, ascending. Can this be done? Thanks in advance. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro - How to not copy blank criteria? | Excel Worksheet Functions | |||
| copy data with criteria | Excel Discussion (Misc queries) | |||
| Copy DSUM Criteria Question | Excel Worksheet Functions | |||
| under certain criteria copy data. | Excel Worksheet Functions | |||
Linear Mode
