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 = ""

Naji wrote:
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 = ""




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!

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" wrote:

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 = ""