Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
len replace part 2
**<__**........<__........SS<__..........<__DD DDDDDDDD<__
**<__**........<__..........<__..SSDDDDDD<__DD DD..DDDD<__ **<__**........<__........SS<__....DDDDDD<__DD DDDDDDDD<__ **<__**........<__SSDDDDDDDD<__DDDDDDDD..<__.. ........<__ **<__**......SS<__DDDDDDDDDD<__DDDDDDDDDD<__.. ........<__ **<__**....SSDD<__DDDDDDDDDD<__DDDDDDDD..<__DD DDDDDDDD<__ Hi all, this brilliant code below worked and because it worked so well, my boss has decided he wants the same for absences for which there are 2 letters D and S, as you can see above. . I can't run them one letter at a time because that'll overwrite the 1st letter. Can anyone change this code show D and S on the one spreadsheet. I've tried and made a mess of it. PS Rick, you were right about the 1/2 days . Thanks again Sub GetHolidayDates() Dim X As Long, Z As Long Dim StartRow As Long, LastRow As Long, Col As Long Dim CodedDate As String StartRow = 1 LastRow = Cells(Rows.Count, "A").End(xlUp).Row For X = StartRow To LastRow Col = 2 CodedDate = Replace(Cells(X, "A").Value, "<__", "....") For Z = 1 To Len(CodedDate) If Mid(CodedDate, Z, 1) = "L" Then If Z And 1 Then If Mid(CodedDate, Z + 1, 1) = "L" Then Cells(X, Col).Value = (Z + 1) / 2 Else Cells(X, Col).Value = ((Z + 1) / 2) & "(am)" End If Col = Col + 1 ElseIf Mid(CodedDate, Z - 1, 1) < "L" Then Cells(X, Col).Value = (Z / 2) & "(pm)" Col = Col + 1 End If End If Next Next End Sub -- Rick (MVP - Excel) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
replace part text in a cell | Excel Discussion (Misc queries) | |||
Using Replace to remove part of a string | Excel Programming | |||
Replace Part of a Function | Excel Worksheet Functions | |||
Replace Old Part Numbers with New Part Numbers in a Macro. | Excel Discussion (Misc queries) | |||
Replace first part of SS# with x's (XXXX-XX-9999) | Excel Programming |