View Single Post
  #6   Report Post  
Posted to microsoft.public.excel.programming
joel joel is offline
external usenet poster
 
Posts: 9,101
Default extracting data from a row

Try this code. I use a STATE variable FOUND to determine when the 1st MR is
found and then set it FALSE when there isn't an MR. The code may need to be
modified slightly for the last time column.

If found = True Then
Cells(RowCount, TimeCol) = Cells(1, ColCount - 1)
Cells(RowCount, TimeCol).NumberFormat = "HH:MM"
End If

the section of code above is to cover the case where MR is in the last Time
column. I don't know if the First "Start" is in the column after the last
Time period or there are some columns inbetween like

Col X Col Y
11:40 PM Start
MR
or

Col X Col Y Col Z
11:40 PM Start
MR

Here is the modified code.

Set c = Rows(1).Find(what:="Start", LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
MsgBox ("Cannot find Start - Exiting Macro")
Exit Sub
End If
StartCol = c.Column

RowCount = 2
Do While Range("A" & RowCount) < ""
TimeCol = StartCol
ColCount = 2
found = False
Do While ColCount < StartCol
Select Case found
Case True:
If Cells(RowCount, ColCount) < "MR" Then
Cells(RowCount, TimeCol) = Cells(1, ColCount - 1)
Cells(RowCount, TimeCol).NumberFormat = "HH:MM"
TimeCol = TimeCol + 1
found = False
End If
Case False:
If Cells(RowCount, ColCount) = "MR" Then
Cells(RowCount, TimeCol) = Cells(1, ColCount)
Cells(RowCount, TimeCol).NumberFormat = "HH:MM"
TimeCol = TimeCol + 1
found = True
End If
End Select
ColCount = ColCount + 1
Loop
If found = True Then
Cells(RowCount, TimeCol) = Cells(1, ColCount - 1)
Cells(RowCount, TimeCol).NumberFormat = "HH:MM"
End If
RowCount = RowCount + 1
Loop


"MJKelly" wrote:

Thanks Joel, that excellent, however, the code is capturing all
instances of the MR status, instead of capturing the start time and
end time of each MR time duration. There will only be up to two MR
timewindows in a row, but it could be none, one or two.

To make it more complicated, the end of the MR time window needs to be
recorded as the column next to the end of the MR. ie a MR of 07:00 to
07:30 will be represented in the data table as below.

07:00 07:10 07:20 07:30
MR MR MR Work


I have tried to do this myself and have written the code below - But,
it is not working, and strangly, if I change the MR times in a record
the code ignores the changed data and outputs the original MR times
(I'm pulling my hair on this one).

Kind regards,
Matt

Sub Find_MR_Times()
'finds the MR start and end times (two different MR's can be
identified)

Dim a As Range
Dim b As Range
Dim c As Range
Dim d As Range
Dim e As Range

Dim MR1Start As Variant
Dim MR1End As Variant
Dim MR2Start As Variant
Dim MR2End As Variant

MR1Start = ""
MR1End = ""
MR2Start = ""
MR2End = ""

Dim MR1StartCol As Integer
Dim MR1Endcol As Integer
Dim MR2StartCol As Integer

MR1StartCol = 0
MR1Endcol = 0
MR2StartCol = 0

For Each a In ThisWorkbook.Sheets("Monday").Range("B4:B20")
If Not a.Value = "" Then
For Each b In
ThisWorkbook.Sheets("Monday").Range(a.Cells(a.Row, 7), a.Cells(a.Row,
150))
If b.Value = "MR" Then
MR1Start = Cells(3, b.Column).Value
a.Offset(0, 152).Value = MR1Start
MR1StartCol = b.Column
Exit For
End If
Next b

If Not MR1Start = "" Then
For Each c In Range(a.Cells(a.Row, MR1StartCol),
a.Cells(a.Row, 150))
If Not c.Value = "MR" Then
MR1End = Cells(3, c.Column).Value
a.Offset(0, 153).Value = MR1End
MR1Endcol = c.Column
Exit For
End If
Next c

If Not MR1Start = "" Then
For Each d In Range(a.Cells(a.Row, MR1Endcol),
a.Cells(a.Row, 150))
If d.Value = "MR" Then
MR2Start = Cells(3, d.Column).Value
a.Offset(0, 154).Value = MR2Start
MR2StartCol = d.Column
Exit For
End If
Next d

If Not MR2Start = "" Then
For Each e In Range(a.Cells(a.Row, MR2StartCol),
a.Cells(a.Row, 150))
If Not e.Value = "MR" Then
MR2End = Cells(3, e.Column).Value
a.Offset(0, 155).Value = MR2End
Exit For

End If
Next e

Else
a.Offset(0, 154).Value = "~"

End If

Else
a.Offset(0, 153).Value = "~"
End If


End If
End If

Next a

MR1Start = ""
MR1End = ""
MR2Start = ""
MR2End = ""

MR1StartCol = 0
MR1Endcol = 0
MR2StartCol = 0

End Sub