Selecting Range based on criteria?

[itipu]

Hi all!

I have a reporting macro/spreadsheet, which dumps some info from Active Directory...

Lets say I have columns A & B, in column A I have a list of machines, in column B I have a list of last logon times...

I know how to e-mail from Excel a range("B15:B20") <-- for example... but what I want to do is to select a range based on the last logon times:

Machine NameLast LogonHOUIC-S-00523/01/2007 16:23AMSDC1-S-60115/01/2007 03:13AMSDC1-S-60019/01/2007 11:37HOUIC-S-09115/02/2007 06:50PHC-S-0101220/02/2007 08:38AMSDC2-S-0136420/02/2007 03:05NOR-S-0100016/02/2007 15:49AMSDC2-S-0157209/02/2007 16:19AMSDC1-S-0143214/02/2007 03:10abe-u-d00068No Local Logon

So first I need to sort data by LastLogon (not sure how to do that programmatically)

Then choose range in a form (A1:Ax;B1,Bx) <-- not sure if thats right, based on data in Last Logon which is
a) equals to "No Local Logon"
b) 2 months less then todays date...

Any suggestions on how to do this?

Your help would be much appreciated! Thanks a lot, Mike

[Bob Phillips]

How about posting a sample workbook, makes life simpler for us?

[itipu]

I had to remove quite a bit and modify it for obvious security reasons... but you can see the code that dumps info from AD & also a sample of how that info is stored!

Thanks a lot

Mike

P.S I marked the duplicate thread as Solved... any other way I can kill it?

[Bob Phillips]

Sub ExtractData()
Const EMAIL_SHEET As String = "_eeMail"
Dim rng As Range
Dim dteTest As Date
Dim sh As Worksheet

With ActiveSheet

dteTest = DateSerial(Year(Date), Month(Date) - 2, Day(Date))

.Columns("A:E").Sort Key1:=.Range("B2"), _
Order1:=xlDescending, _
Header:=xlYes
Set rng = .Range(.Range("A1"), .Range("A1").End(xlDown)).Resize(, 4)
rng.AutoFilter
.Columns("A:E").AutoFilter Field:=2, _
Criteria1:="<>No Local Logon", _
Operator:=xlAnd, _
Criteria2:=">" & Format(dteTest, Cells(rng.Rows.Count, 2).NumberFormat)
Set rng = rng.SpecialCells(xlCellTypeVisible)
On Error Resume Next
Set sh = Worksheets(EMAIL_SHEET)
On Error GoTo 0
If sh Is Nothing Then
Set sh = Worksheets.Add
sh.Name = EMAIL_SHEET
End If
sh.Cells.ClearContents
rng.Copy sh.Range("A1")

End With
End Sub

[itipu]

Thanks a lot,

It does work, however, it uses With ActiveSheet in my case, latest AD dump is not usually an ActiveSheet, as Sheet3(Extract) is set to be ActiveSheet by default for obvious reasons :) Also there maybe a couple of Computer sheets at any given time, the naming convention is :

ActiveSheet.Name = "Computer " & Format(Date, "DD.MM.YYYY") & " at " & Format(Time, "hh.mm")

So perhaps it is possible to make script pick the latest one...

Also this is because I have other reports in this script which I removed...

Also it filters all machines nicely, but it doesn't list the once that are marked as "No Last Logon" :(

But thank you very much this is great!

Thanks

Mike

[itipu]

Fixed my self the issue with No Local Logon:

Criteria1:="=No Local Logon", _
Operator:=xlOr, _

A couple of other things: where do you specify exactly (2 months difference) in case I want to change it to 3 months, or 1 month...

Also apart from the ActiveSheet issue it also ammends the ActiveSheet, because once it generates EMAIL_SHEET with all the data it also sets filter on the ActiveSheet itself and does not undo that when done :)

Thanks you so much!!!!!!!!!


Mike

[Bob Phillips]

A couple of other things: where do you specify exactly (2 months difference) in case I want to change it to 3 months, or 1 month...




dteTest = DateSerial(Year(Date), Month(Date) - 2, Day(Date))

[itipu]

Thanks a lot again... still trying to figure out though how to set ActiveSheet to the sheet named

"Computer " & Format(Date, "DD.MM.YYYY") & " at " & Format(Time, "hh.mm")

Whereby dd.mm.yyyy should be todays date, and Time can be ignored...

basically so that you can generate EMAIL_SHEET from anywhere within the macro....

Thanks a lot

Mikey

[itipu]

I have added the following proceedure, so now it is possible to choose on which sheet to perfom the operation...

Sub Go2sheet()
myShts = ActiveWorkbook.Sheets.Count
For i = 4 To myShts
myList = myList & i & " - " & ActiveWorkbook.Sheets(i).Name & " " & vbCr
Next i
Dim mySht As Single
mySht = InputBox("Select sheet to generate SC ticket from." & vbCr & vbCr & myList, "Report & Service Centre Ticket")
Sheets(mySht).Select
End Sub

However, every time I press cancel on InputBox or close it using X it crashes, with the following:

Run Typer error 13
Type Mismatch

On the following line:

mySht = InputBox("Select sheet to generate SC ticket from." & vbCr & vbCr & myList, "Report & Service Centre Ticket")

I tried to do some On Error stuff but to no effect... any idea what this is? I call the proceedure as follows:

Sub ExtractData()
Const EMAIL_SHEET As String = "_eeMail"
Dim rng As Range
Dim dteTest As Date
Dim sh As Worksheet

Go2sheet

With ActiveSheet

Thanks a lot

Mike