For Next Variable Not Being Passed Through For Second Sheet

[Naji]

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

 

[Bob Phillips]

Shouldn't you run the loops the other way around?

For Sht = 1 To 2

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

Do While RowCount <= LastRow

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

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


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Naji]

No, because for each unit it goes through the two sheets, then goes
back to the next unit.

See below: Everything is offset according to where the 1/1/08 date
range is. The output for this should be:

Unit 1 Up Shift 3 1/1/08
Down Shift 1 1/3/08
Up Shift 3 1/3/08
Down Shift 3 1/4/08


1/1 1/2 1/3 1/4
Unit 1 Shift 1 0 X 0 X
Shift 2 0 X 0 X
Shift 3 X X X 0


1/1 1/2 1/3
Unit 2 Shift 1 0 X 0 X
Shift 2 0 X 0 X
Shift 3 X X X 0

Where it looks for X's to count as UPTURNS and BLANKS to COUNT FOR
DOWNTURNS. It detects a change from UP to down or vice versa and
outputs it to a .dat file. It will go through sheets 1 to 2 for UNIT 1
before going back to the initial sheet to start over with unit 2. The
units need to be output together. I am having problems with the first
turn tho. I want Previous Shift Status to refer to the value in cell
C6 when it starts each sheet and since STARTINGDATERANGE refers to
cell C4, I figured doing:

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

Would do the trick of putting what the last turn was on the previous
shift, "up or down", so that it can be a continuation of sheet 1 for
sheet 2 and for sheet 1 it would use that cell to make sure that it
starts on a UPTURN and not DOWNTURN. For sht one I would put "D" for
C6 and in sht 2 I would put in the last shift status of sht 1.

Am I going about this the wrong way?

Also, on the last post I noted that it does the first sheet ok. I was
wrong. This declaration doesn't work for either sheets. What's going
on here?

Thanks!

 

[Dave D-C]

ISTMAFB (It Seems To Me At First Blush)
that a range variable has the sheet within it, and so you
have to break it apart to use the ADDRESS part on
another sheet. This shows what I'm thinking:

Sub Sub1()
Dim rng1 As Range, rng2 As Range
Set rng1 = Sheets("Sheet1").Range("a1:b3")
MsgBox rng1.Parent.Name & " | " & rng1.Address
' same address, different sheet:
Set rng2 = Sheets("Sheet2").Range(rng1.Address)
MsgBox rng2.Parent.Name & " | " & rng2.Address
End Sub ' Dave D-C

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?

[snip]

 

[Naji]

Sorry Dave,

I'm not quite sure what is meant by your response.

How would this apply to my problem?

ISTMAFB (It Seems To Me At First Blush)
that a range variable has the sheet within it, and so you
have to break it apart to use the ADDRESS part on
another sheet.  This shows what I'm thinking:

Sub Sub1()
  Dim rng1 As Range, rng2 As Range
  Set rng1 = Sheets("Sheet1").Range("a1:b3")
  MsgBox rng1.Parent.Name & " | " & rng1.Address
  ' same address, different sheet:
  Set rng2 = Sheets("Sheet2").Range(rng1.Address)
  MsgBox rng2.Parent.Name & " | " & rng2.Address
End Sub ' Dave D-C


[snip]>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?

[snip]

 

[Dave D-C]

I'm sorry.
My post obviously doesn't apply. Dave

Sorry Dave,
I'm not quite sure what is meant by your response.
How would this apply to my problem?

ISTMAFB (It Seems To Me At First Blush)
that a range variable has the sheet within it, and so you
have to break it apart to use the ADDRESS part on
another sheet.  This shows what I'm thinking:

Sub Sub1()
  Dim rng1 As Range, rng2 As Range
  Set rng1 = Sheets("Sheet1").Range("a1:b3")
  MsgBox rng1.Parent.Name & " | " & rng1.Address
  ' same address, different sheet:
  Set rng2 = Sheets("Sheet2").Range(rng1.Address)
  MsgBox rng2.Parent.Name & " | " & rng2.Address
End Sub ' Dave D-C


[snip]>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?

[snip]