Option Explicit
Sub JobRateUpdate()
Dim ws As Worksheet 'Your worksheet
Dim lRow As Long 'Worksheet's last row
Dim startRng As Long 'The 1st emp. row @ currRate
Dim endRng As Long 'The last emp. row @ currRate
Dim currCol As Integer 'Column S, T, or U
Dim i, j As Integer 'Loop counters
Set ws = Application.ThisWorkbook.Worksheets("JobSheet")
lRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
currCol = 19 'S Column
For i = 2 To lRow
For j = i To lRow 'Find the last row that houses the job data
If ws.Cells(j, 2) <> "" Then
endRng = j
Exit For
End If
Next j
For startRng = i To endRng 'Loop through emp rate's range and copy data
If ws.Cells(endRng, currCol) = "" Then 'if S is blank, goto T col
currCol = currCol + 1
If ws.Cells(endRng, currCol) = "" Then 'if T is blank, goto U col
currCol = currCol + 1
If ws.Cells(endRng, currCol) = "" Then 'if U is blank, goto S col
currCol = 19
End If
End If
End If
MsgBox "i: " & i & " | lRow: " & lRow & " | Emp No: " & ws.Cells(i, 1) & _
vbNewLine & vbNewLine & _
"startRng: " & startRng & " | endRng: " & endRng & _
vbNewLine & vbNewLine & _
"currCol: " & currCol
ws.Cells(startRng, 4) = VBA.Left(ws.Cells(endRng, currCol), _
VBA.InStr(1, ws.Cells(endRng, currCol), " ") - 1)
ws.Cells(startRng, 5) = VBA.Mid(ws.Cells(endRng, currCol), _
VBA.InStr(1, ws.Cells(endRng, currCol), " ") + 1)
If currCol < 21 Then
currCol = currCol + 1
End If
Next startRng
currCol = 19 'Reset back to S col
i = endRng 'Advance counter to the end of the current emp rate's rng
Next i
MsgBox "Out of the loop"
Set ws = Nothing
End Sub