Loop de loop

Topic

New Reply

I have a spreadsheet in Excel 2003, that lists employees of a call site, and tracks every time a call is monitored. It’s by date, so each employee name appears multiple times. What I want to do is check the first instance of each name, see if that monitoring is “OK”, if so go to the next instance of their name and do the same thing. If they have a monitoring that is “NG” then I want to delete all instances of that employee’s name from the spreadsheet. The spreadsheet can be refreshed, so that’s not a problem. What I want to end up with is literally the last man standing, a listing of the one employee (if there is one) who received an “OK” every time he or she was monitored for a given period of time.

I’ve tried several ways, none of which have worked. I’ve tried doing a FIND ALL in code, which would find all instances of a single employee, but when I deleted the row with the employee’s name it broke the loop. Also when that loop was put into a FOR NEXT loop, to get the next employee, it stopped working entirely. I tried using another spreadsheet in the workbook that is used for pulling monitorings of individual employees, but couldn’t get the loop to work on the listbox.

I’m up for any and all suggestions at this point, and any help will be greatly appreciated. Thanks.

Reply | Quote

Replies

Here’s a solution you may be able to use: The macro in this worksheet will do an advanced filter to pull out a unique list of names. It then creates a work area with Name and NG as headers and will copy the names and place a SumProduct formula that will count the NGs by name. The work area is then copied and replaced with Values {sorting with the formulas active doesn’t work!}. The list is then sorted Ascending by NG count then Name Apha so your star performers percoluate to the top.

The macro depends on some dynamic named ranges {see Define Names}.
You can paste new data into the Data sheet then switch to the Calculate sheet, enter your date range and run the macro.

If this doesn’t solve your problem maybe it will at least give you some ideas.

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

There can be several solutions for what is needed …

Here is a Pivot Table to see a count of each employee’s NG and OK. It shows a blank for the employee with no “NG”.
After reviewing Pivot Table, in the raw data Sort and Delete any employee that has an “NG”

Reply | Quote

Tim,

I tried the Pivot Table first also but couldn’t figure out how to get it to only work with a given date range vs the entire data set. Any Ideas how to do this with the Pivot Table?

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi RG – There are a few ways to limit output by date range. One of the ways it can be done is with the revised attached. Just double click “Date” and choose dates to hide. There are many more alternatives such as using the Date within the Pivot Table.

Reply | Quote

Tim,

Duh {see me whacking myself on the forehead}!

Thanks for the comeback!

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Thanks for all of your input. Here’s the code I finally used. The “N” = NG and the “Y” = OK., and are in a field called Admin The sort is by name and by Admin, so N comes before Y. When it’s finished running, the only row left on the spreadsheet (of 1275 to start) is the winner. Unfortunately, in this case, after 3 days of coding to find the winner, we discovered the winner no longer works there.

Private Sub btnallperf_Click()

Dim intRows As Integer
Dim strName As String
Dim strFound As String
Dim strWinner As String
Dim myrange As Range
Dim strHigh As Integer
Dim strHighName As String
Dim strHighTest As String

Set oRange = Range(Range(“$a$17”), Range(“$e$1300”).End(xlUp))
intRows = Application.WorksheetFunction.CountA(Intersect(oRange, Range(“e:e”)))

Application.ScreenUpdating = False

‘Clear the contents of any previous searches

Columns(“U:U”).Select
Selection.ClearContents
Range(“e17”).Select

‘Sort the data on the spreadsheet by name, and then by Admin (“N” comes before “Y”)

Range(“A17:T1500”).Sort Key1:=Range(“E17”), Order1:=xlAscending, Key2:= _
Range(“T17”), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal

‘Loop through the names

For i = 1 To intRows

If ActiveCell “” Then

If ActiveCell = ActiveCell.Offset(1, 0) Then

‘If there is a single “N” then delete all rows for that employee

If ActiveCell.Offset(0, 15) = “N” Then
Do Until ActiveCell ActiveCell.Offset(1, 0)
Rows(ActiveCell.Row).Select
Selection.Delete shift:=xlUp
ActiveCell.Offset(0, 4).Select
Loop

Rows(ActiveCell.Row).Select
Selection.Delete shift:=xlUp
ActiveCell.Offset(0, 4).Select

‘If there is no “N” in Admin then start numbering the rows of “Y”

Else
ActiveCell.Offset(0, 16) = 1
Do Until ActiveCell ActiveCell.Offset(1, 0)
If ActiveCell = ActiveCell.Offset(1, 0) Then
If ActiveCell.Offset(0, 16) “” Then
ActiveCell.Offset(1, 16) = ActiveCell.Offset(0, 16) + 1
ActiveCell.Offset(1, 0).Select
End If
End If
Loop

End If

If ActiveCell.Offset(0, 16) >= 1 Then
ActiveCell.Offset(1, 0).Select
End If

Else
If ActiveCell.Offset(0, 15) = “N” Then
Rows(ActiveCell.Row).Select
Selection.Delete shift:=xlUp
ActiveCell.Offset(0, 4).Select

Else
ActiveCell.Offset(0, 16) = 1
Do Until ActiveCell ActiveCell.Offset(1, 0)
If ActiveCell = ActiveCell.Offset(1, 0) Then
If ActiveCell.Offset(0, 16) “” Then
ActiveCell.Offset(1, 16) = ActiveCell.Offset(0, 16) + 1
ActiveCell.Offset(1, 0).Select
End If
End If
Loop

ActiveCell.Offset(1, 0).Select
End If
End If

Else

‘Indicate the winner

strHigh = Application.WorksheetFunction.Max(Range(“u17:u1500”))

Range(“u17”).Select
Do Until ActiveCell = “”
If ActiveCell < strHigh Then
Rows(ActiveCell.Row).Select
Selection.Delete shift:=xlUp
ActiveCell.Offset(0, 20).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Loop

Application.ScreenUpdating = True

End

End If
Next i

End Sub

Reply | Quote