Figuring out loop for a production schedule

N

Naji

There is probably a simple solution to this, but everything I've tried
doesn't work. So, I have a spreadsheet file that looks through a
scheduling spreadsheet looking for X's and Blank spots to indicate up
or downtime. It reads the information and then writes it into a CSV
file. Right now the loop ends when the Unit number is blank and it
reads everything else. I need it to skip certain unit numbers. For
example, I want it to go through all the schedules and only worry about
writing units 86 and 10 and a couple others into a CSV file, bypassing
other irrelevant unit numbers. How would you do this ? Here is the code
I have:

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

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

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

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

Do

PreviousShiftStatus = "No Previous Status"

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

If Not ShiftStatus(ShiftItem) Like "[XEO]" Then
If Len(Trim(ShiftStatus(ShiftItem))) <> 0 Then
sh.Cells(ShiftRow + (ShiftItem - 1),
CurrentColumn).Select
Stop
'problem with range sh.Cells(ShiftRow +
(ShiftItem -1), CurrentColumn)
'raise error, notify user and select the
problem range, exit
End If
End If

Select Case Trim(UCase(ShiftStatus(ShiftItem)))
Case "X", "O"
CurrentShiftStatus = "UP"
Case ""
CurrentShiftStatus = "DOWN"
Case "E"
CurrentShiftStatus = "DOWN"
ConservationShutdown = True
End Select

If PreviousShiftStatus <> CurrentShiftStatus Then
'status changed
If ConservationShutdown Then
Print #FileNumber, " " & UnitNumber & "," &
"DOWN" & "," & _
Format(CurrentDate + #12:00:00 PM#,
"mm/dd/yyyy hh:mm")
Print #FileNumber, " " & UnitNumber & "," &
"UP" & "," & _
Format(CurrentDate + #6:00:00 PM#,
"mm/dd/yyyy hh:mm")
CurrentShiftStatus = "UP"
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

Set DataRange = DataRange.Offset(6)
UnitNumber = DataRange(1).Offset(, -2)
ShiftRow = DataRange(1).Row
CurrentDate = StartingDateRange
Loop Until UnitNumber = ""
 
N

Naji

Naji said:
There is probably a simple solution to this, but everything I've tried
doesn't work. So, I have a spreadsheet file that looks through a
scheduling spreadsheet looking for X's and Blank spots to indicate up
or downtime. It reads the information and then writes it into a CSV
file. Right now the loop ends when the Unit number is blank and it
reads everything else. I need it to skip certain unit numbers. For
example, I want it to go through all the schedules and only worry about
writing units 86 and 10 and a couple others into a CSV file, bypassing
other irrelevant unit numbers. How would you do this ? Here is the code
I have:

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

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

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

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

Do

PreviousShiftStatus = "No Previous Status"

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

If Not ShiftStatus(ShiftItem) Like "[XEO]" Then
If Len(Trim(ShiftStatus(ShiftItem))) <> 0 Then
sh.Cells(ShiftRow + (ShiftItem - 1),
CurrentColumn).Select
Stop
'problem with range sh.Cells(ShiftRow +
(ShiftItem -1), CurrentColumn)
'raise error, notify user and select the
problem range, exit
End If
End If

Select Case Trim(UCase(ShiftStatus(ShiftItem)))
Case "X", "O"
CurrentShiftStatus = "UP"
Case ""
CurrentShiftStatus = "DOWN"
Case "E"
CurrentShiftStatus = "DOWN"
ConservationShutdown = True
End Select

If PreviousShiftStatus <> CurrentShiftStatus Then
'status changed
If ConservationShutdown Then
Print #FileNumber, " " & UnitNumber & "," &
"DOWN" & "," & _
Format(CurrentDate + #12:00:00 PM#,
"mm/dd/yyyy hh:mm")
Print #FileNumber, " " & UnitNumber & "," &
"UP" & "," & _
Format(CurrentDate + #6:00:00 PM#,
"mm/dd/yyyy hh:mm")
CurrentShiftStatus = "UP"
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

Set DataRange = DataRange.Offset(6)
UnitNumber = DataRange(1).Offset(, -2)
ShiftRow = DataRange(1).Row
CurrentDate = StartingDateRange
Loop Until UnitNumber = ""
Click to expand...




Anyone? Is this not possible or just nobody replied? I need it to loop
from beginning until end, and in addition to filter out certain unit
numbers. Any help or direction would be greatly appreciated!
 
G

Guest

Hi,

Set DataRange = sh.Range(StartingDateRange.Offset(1), _
This part >>>> StartingDateRange.End(xlToRight).Offset(3))
xlToRight only goes as far as data is in each cell. then Offset(3) ,
I am not sure maybe offset(0,3) is better but when a blank cell is reached
you then jump 3 columns to the right?

You can send me all details on what you want done and I can maybe create a
userform utility for you.

You can see my Excel stuff at:

http://www.geocities.com/excelmarksway

Why do you write it to a CSV? Where does it go to as a CSV file? Maybe ACCESS?

- mark

Naji said:
There is probably a simple solution to this, but everything I've tried
doesn't work. So, I have a spreadsheet file that looks through a
scheduling spreadsheet looking for X's and Blank spots to indicate up
or downtime. It reads the information and then writes it into a CSV
file. Right now the loop ends when the Unit number is blank and it
reads everything else. I need it to skip certain unit numbers. For
example, I want it to go through all the schedules and only worry about
writing units 86 and 10 and a couple others into a CSV file, bypassing
other irrelevant unit numbers. How would you do this ? Here is the code
I have:

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

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

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

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

Do

PreviousShiftStatus = "No Previous Status"

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

If Not ShiftStatus(ShiftItem) Like "[XEO]" Then
If Len(Trim(ShiftStatus(ShiftItem))) <> 0 Then
sh.Cells(ShiftRow + (ShiftItem - 1),
CurrentColumn).Select
Stop
'problem with range sh.Cells(ShiftRow +
(ShiftItem -1), CurrentColumn)
'raise error, notify user and select the
problem range, exit
End If
End If

Select Case Trim(UCase(ShiftStatus(ShiftItem)))
Case "X", "O"
CurrentShiftStatus = "UP"
Case ""
CurrentShiftStatus = "DOWN"
Case "E"
CurrentShiftStatus = "DOWN"
ConservationShutdown = True
End Select

If PreviousShiftStatus <> CurrentShiftStatus Then
'status changed
If ConservationShutdown Then
Print #FileNumber, " " & UnitNumber & "," &
"DOWN" & "," & _
Format(CurrentDate + #12:00:00 PM#,
"mm/dd/yyyy hh:mm")
Print #FileNumber, " " & UnitNumber & "," &
"UP" & "," & _
Format(CurrentDate + #6:00:00 PM#,
"mm/dd/yyyy hh:mm")
CurrentShiftStatus = "UP"
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

Set DataRange = DataRange.Offset(6)
UnitNumber = DataRange(1).Offset(, -2)
ShiftRow = DataRange(1).Row
CurrentDate = StartingDateRange
Loop Until UnitNumber = ""
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Change sheets within a read and write function... 14
For Next Variable Not Being Passed Through For Second Sheet 5
Help - Loop Function Giving Incorrect Output 4
Need Help With My Loop That Creates Output File 5
Adding Time to a Date 1
IsDayLightSavingTime doesn't work for 2007 and beyond 3
add an appointment for a custom calendar, vba excel 2
Passing a Date Variable from Code to Update Query in a Loop 7

Top