I am stuck on this section of code which has me perplexed.

I have a loop set up that goes through a range of cells and picks up
changes in production turns. It goes through a range in sheet one and
detects changes in "X" and " ", so if it goes from " " to "X", it
means the unit is going up and running and then a line is outputted
into a .dat file. It does this for multiple units. There are multiple
units in each sheet, but each unit needs to go through each sheet
separately. So for Unit "1A" for example, it goes through sht1 and
then sht2 and then goes back to sht1 for Unit "2A", etc.

For some reason, my code is only picking up my PreviousShiftStatus
declaration in Sht1, but not in the following sheet...why is that?

I am specifically referring to the line:

PreviousShiftStatus = DataRange(1).Offset(-2, -2)

This variable is only assigned for sht1, but when it goes through
sht2, it doesn't do anything. Why? Since DataRange(1) is set to cell
C4, doing this offset shouldn't this offset set it to C6 for sht1 when
it goes through it and then sht2 when it goes through as well?


Any feedback greatly appreciated...


Sub ProcessRanges()

On Error GoTo ExitSub
Dim StartingDateRange As Range, FileName As String
Dim FileNumber As Integer
Dim Unit As Integer


Debug.Print ThisWorkbook.Path
FileName = "\\broner\data$\FCDM.dat"


FileNumber = FreeFile()
Open FileName For Output As #FileNumber


LastRow = Sheet1.Range("C" & Rows.Count).End(xlUp).Row
RowCount = 0

Do While RowCount <= LastRow


Set StartingDateRange = Sheet1.Range("C" & (RowCount + 3))


For Sht = 1 To 2

If CreateCVS(Sheets("FC" & Sht), StartingDateRange, FileNumber)
Then

'all is well
Debug.Print "Success..."
Else
'problem
Debug.Print "Failure..."
End If


Next Sht





Next Sht

Set StartingDateRange = Sheet2.Range("C" & (RowCount + 3))


For Sht = 1 To 2

If CreateCVS(Sheets("FC" & Sht), StartingDateRange, FileNumber)
Then
'all is well
Debug.Print "Success..."
Else
'problem
Debug.Print "Failure..."
End If


Next Sht



Set StartingDateRange = Sheet3.Range("C" & (RowCount + 3))


For Sht = 1 To 2

If CreateCVS(Sheets("FC" & Sht), StartingDateRange, FileNumber)
Then
'all is well
Debug.Print "Success..."
Else
'problem
Debug.Print "Failure..."
End If


Next Sht



RowCount = RowCount + 7

Loop


ExitSub:
Close #FileNumber


End Sub




Private Function CreateCVS( _
sh As Worksheet, _
StartingDateRange As Range, _
FileNumber As Integer) As Boolean


On Error GoTo Err_CreateCVS
Dim UnitNumber As String, CurrentDate As Date
Dim DataRange As Range


Dim FirstColumn As Integer, LastColumn As Integer, _
CurrentColumn As Integer


Dim ShiftRow As Long, ShiftStatus(1 To 3) As String
Dim ShiftItem As Integer
Dim PreviousShiftStatus As String, CurrentShiftStatus As String
Dim ConservationShutdown As Boolean
Dim HalfDay As Boolean


Dim i As Integer


'Data Range starts with first schedule box. Everything else is
'offset according to this cell


Set DataRange = sh.Range(StartingDateRange.Offset(1), _
StartingDateRange.End(xlToRight).Offset(3))


Debug.Print DataRange(1).Address


FirstColumn = DataRange(1).Column
LastColumn = FirstColumn + DataRange.Columns.Count - 1
ShiftRow = DataRange(1).Row
UnitNumber = DataRange(1).Offset(, -2)
CurrentDate = DateValue(StartingDateRange)





If UnitNumber < "0" Then


PreviousShiftStatus = DataRange(1).Offset(-2, -2)


For CurrentColumn = FirstColumn To LastColumn




ShiftStatus(1) = sh.Cells(ShiftRow, CurrentColumn)
ShiftStatus(2) = sh.Cells(ShiftRow + 1, CurrentColumn)
ShiftStatus(3) = sh.Cells(ShiftRow + 2, CurrentColumn)


For ShiftItem = 1 To 3


ConservationShutdown = False


Select Case Trim(UCase(ShiftStatus(ShiftItem)))
Case "X", "O"
CurrentShiftStatus = "U"
Case "", "H"
CurrentShiftStatus = "D"
Case "E"
CurrentShiftStatus = "D"
ConservationShutdown = True

Case "1/2", "0.5"


If PreviousShiftStatus = "U" Then
CurrentShiftStatus = "D"
Print #FileNumber, UnitNumber & "," &
CurrentShiftStatus & "," & _
Format(CurrentDate +
Choose(ShiftItem, #4:00:00 AM#, #12:00:00 PM#, #8:00:00 PM#), _
"mm/dd/yyyy hh:mm")
PreviousShiftStatus =
CurrentShiftStatus
Else
CurrentShiftStatus = "U"
Print #FileNumber, UnitNumber & "," &
CurrentShiftStatus & "," & _
Format(CurrentDate + Choose(ShiftItem,
#4:00:00 AM#, #12:00:00 PM#, #8:00:00 PM#), _
"mm/dd/yyyy hh:mm")

PreviousShiftStatus =
CurrentShiftStatus

End If




End Select


If PreviousShiftStatus < CurrentShiftStatus Then

If ConservationShutdown Then

Print #FileNumber, UnitNumber & "," &
"D" & "," & _
Format(CurrentDate + #12:00:00
PM#, "mm/dd/yyyy hh:mm")
Print #FileNumber, UnitNumber & "," &
"U" & "," & _
Format(CurrentDate + #6:00:00 PM#,
"mm/dd/yyyy hh:mm")
CurrentShiftStatus = "U"

'################################################# ####################################
'edited-added 2/22/2006

'################################################# ####################################
ElseIf
Trim(UCase(ShiftStatus(ShiftItem))) = "1/2" Then
Print #FileNumber, UnitNumber &
"," & CurrentShiftStatus & "," & _
Format(CurrentDate +
Choose(ShiftItem, #4:00:00 AM#, #12:00:00 AM#, #8:00:00 PM#), _
"mm/dd/yyyy hh:mm")

'################################################# ####################################

'################################################# ####################################


Else




Print #FileNumber, UnitNumber & "," &
CurrentShiftStatus & "," & _
Format(CurrentDate +
Choose(ShiftItem, #12:00:00 AM#, #8:00:00 AM#, #4:00:00 PM#), _
"mm/dd/yyyy hh:mm")



End If


End If






PreviousShiftStatus = CurrentShiftStatus

Next


CurrentDate = CurrentDate + 1


Next


CreateCVS = True
Exit Function


End If


Err_CreateCVS:


End Function