ExcelBanter - For Next Variable Not Being Passed Through For Second Sheet

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - For Next Variable Not Being Passed Through For Second Sheet (https://www.excelbanter.com/excel-programming/404113-next-variable-not-being-passed-through-second-sheet.html)

For Next Variable Not Being Passed Through For Second Sheet

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

For Next Variable Not Being Passed Through For Second Sheet

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" wrote in message
...
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

For Next Variable Not Being Passed Through For Second Sheet

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!

On Jan 11, 11:17*am, "Bob Phillips" wrote:
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" wrote in message

...

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- Hide quoted text -

- Show quoted text -

For Next Variable Not Being Passed Through For Second Sheet

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

Naji wrote:
[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]

For Next Variable Not Being Passed Through For Second Sheet

Sorry Dave,

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

How would this apply to my problem?

On Jan 11, 1:12*pm, Dave D-C wrote:
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

wrote:

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

For Next Variable Not Being Passed Through For Second Sheet

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

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

On Jan 11, 1:12*pm, Dave D-C wrote:
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

wrote:

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