PDA

View Full Version : List codes with the same conditions



Ethan
10-04-2011, 02:21 AM
Hi, I'd like in cells E,F,G to list the codes of shifts that end the next day.
In below example that are shifts: N, F and R

The codes are not always the same, so the formula should check the timings in columns B and C.


. A B C D E F G
1 D 6:00 14:30 N F R
2 N 22:30 6:30
3 E 14:00 22:30
4 F 20:30 4:30
5 R 0:00 8:00

BigJC
10-04-2011, 02:27 AM
Maybe something For each row in range (rows 1-5) where C < B Output A to the next available cell.

I could code it up for you if you need.

BigJC
10-04-2011, 02:36 AM
Just wrote this up really quick, hopefully works first go.


Option Explicit

Sub CalculateCodes()
Dim ws As Worksheet
Dim LastRow As Integer
Dim rw As Integer
Dim colOutput As Integer

'Set the active worksheet
Set ws = ThisWorkbook.Worksheets("Sheet Name")

'Get the last row from column A
LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row

'Set initial column to 5 (E)
colOutput = 5

For Each rw In ws.Range("A1", "A" & LastRow)
If ws.Cells(rw, 3).Value < ws.Cells(rw, 2).Value Then
'Write the code to the next output cell
ws.Cells(1, colOutput).Value = ws.Cells(rw, 1).Value
'Increment the output column by 1
colOutput = colOutput + 1
End If
Next

End Sub