Need help on a for loop

Hello,
In the following code, I want to append a row to a csv file provided that rng.value <> 0 Or rng.value <> "".  If rng.value = 0 or rng.value = "", I want to go to the next row.  I have two spreadsheet, I am processing.  Each one has a zero in two rows in the relevant cell. And there are 7 rows in each spread sheet for a total of 14 rows for both spreadsheets.  When I run the code I want to see on 10 rows in the resulting csv sheet because two rows in each spreadsheet have a value of zero in the designated cell.  However, I am still getting 14 rows in the final output.  I have stepped through the code and I am executing the goto statement correctly, but it appears that the value from the adjacent cell is being transposed to the cell that previously had a value of zero.  
Public Function RangeToCSV(list As Range) As String
    ' Comments:
    ' Params  : list
    ' Returns : String
    ' Modified:

    'TVCodeTools ErrorEnablerStart
    On Error GoTo PROC_ERR
    'TVCodeTools ErrorEnablerEnd

    Dim strTmp As String
    Dim lngCurrentRow As Long
    Dim lngCurrentColumn As Long
    Dim rng As Range
    Dim xlsRange As Range

    If TypeName(list) = "Range" Then
        lngCurrentRow = 15

        For Each rng In list.Cells
            If Range(rng.Address).Column > 14 Then
                If Range(rng.Address).Column = 19 Then
                    If rng.value = 0 Or rng.value = "" Then
                       ' Debug.Print "Yes"
                        GoTo SkipProcessing
                    End If
                End If

                If rng.row = lngCurrentRow Then
                    If strTmp = vbNullString Then
                        strTmp = rng.value
                    Else
                        strTmp = strTmp & "," & rng.value
                    End If
                Else
                    lngCurrentRow = lngCurrentRow + 1
                    If strTmp = vbNullString Then
                        strTmp = rng.value
                    Else
                        strTmp = strTmp & Chr(10) & rng.value
                    End If
                End If
 
            End If
SkipProcessing:
        Next
    End If

    RangeToCSV = strTmp

    'TVCodeTools ErrorHandlerStart
PROC_EXIT:
    Application.EnableEvents = True
    Exit Function

PROC_ERR:
    MsgBox Err.Description, vbCritical, "Sheet1.RangeToCSV"
    Resume PROC_EXIT
    'TVCodeTools ErrorHandlerEnd

End Function

Open in new window

Juan VelasquezAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
I don't know if it helps, but using GoTo's like that is not considered good programming practice. How about this instead?

      For Each Rng In List.Cells
            If Range(Rng.Address).Column > 14 Then
                If Range(Rng.Address).Column = 19 Then
                    If Rng.Value = 0 Or Rng.Value = "" Then
                       ' Debug.Print "Yes"
                    End If
                ElseIf Rng.Row = lngCurrentRow Then
                    If strTmp = vbNullString Then
                        strTmp = Rng.Value
                    Else
                        strTmp = strTmp & "," & Rng.Value
                    End If
                Else
                    lngCurrentRow = lngCurrentRow + 1
                    If strTmp = vbNullString Then
                        strTmp = Rng.Value
                    Else
                        strTmp = strTmp & Chr(10) & Rng.Value
                    End If
                End If
 
            End If
        Next

Open in new window

0
Juan VelasquezAuthor Commented:
Hello Martin,

I tried the code you sent and now there are no rows in the output
0
Juan VelasquezAuthor Commented:
Oh I see what the problem is.  I'll try it again
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Martin LissOlder than dirtCommented:
Okay, sorry, without the workbook I couldn't test. The point is still the same and that is that by properly using If's (which I apparently failed to do) you can avoid the GoTo.
0
Juan VelasquezAuthor Commented:
I've tried it again and it looks like what is happening now is that the values in column F are being transposed to column E as shown in the attached output file
OutputFile.csv.xlsx
0
Martin LissOlder than dirtCommented:
I'm going out and I'll get back to you in a few hours.
0
Juan VelasquezAuthor Commented:
Hello,

Something just occurred to me.   Maybe i could test for the value of the critical cell before it start processing the first cell of a row
0
Martin LissOlder than dirtCommented:
Since there's no code in the workbook you attached and there's only one zero anywhere in the ws (Cell X1) which is different than your original problem statement I'm a little confused. The easiest thing to do would be for you to tell me which rows (by number) you don't want in your output, and why you don't want them.
0
Juan VelasquezAuthor Commented:
Hello Martin

I've attached the workbook.  The code in question is executed via the Approve Time button on the Summary Sheet.  The code is located in the summary sheet.  I've also attached the output with the rows I want deleted, highlighted in Yellow.  Those roles need to be deleted because there is a zero in column E.  Open that file in Excel to view the highlighted columns
Test-Timesheets-Workbook.xlsm
OutputFile.csv
0
Harry LeeCommented:
Chtullu135,

If you want your For loop to go to next row, use

"Resume Next" instead of "GoTo SkipProcessing".

Try it and let me know how it goes.
0
Juan VelasquezAuthor Commented:
When I put the Resume Next in the code you posted,  I get a message stating resume without error. and the file produced is blank.  When I put it in my original code, I get the same error message and I get a blank file
Public Function NewRangeToCSV(list As Range) As String

    Dim strTmp As String
    Dim lngCurrentRow As Long
    Dim lngCurrentColumn As Long
    Dim rng As Range
    Dim xlsRange As Range
    
If TypeName(list) = "Range" Then
    lngCurrentRow = 15
          For Each rng In list.Cells
            If Range(rng.Address).Column > 14 Then
                If Range(rng.Address).Column = 19 Then
                    If rng.value = 0 Or rng.value = "" Then
                       Resume Next
                    End If
                ElseIf rng.row = lngCurrentRow Then
                    If strTmp = vbNullString Then
                        strTmp = rng.value
                    Else
                        strTmp = strTmp & "," & rng.value
                    End If
                Else
                    lngCurrentRow = lngCurrentRow + 1
                    If strTmp = vbNullString Then
                        strTmp = rng.value
                    Else
                        strTmp = strTmp & Chr(10) & rng.value
                    End If
                End If
 
            End If
        Next
                                            
    End If
NewRangeToCSV = strTmp
End Functio

Open in new window

0
Martin LissOlder than dirtCommented:
I'm finding that stepping through the code using the rng object is difficult. I'm sure I can produce the CSV you need in a different way so I have the following questions:

1) Will the format of the WTS sheets always be the same? In other words the data will always be for a week and it is shown in two sections the first in A14:N21 and the second in G26:M35?

2) Is it true that if a value in column G is zero or blank that you don't want any data for that day in the CSV

3) Given a row with hours > 0, like row 15 on WTS_Don Johnson, can you tell me which cells should be in the CSV file (and in the proper order)?
0
Juan VelasquezAuthor Commented:
Hello Martin
With respect to point 1.  The number of rows may vary in that users may generate mulitple rows for the same day.  For example, he or she may work 8 hour regular time on a Monday and then work an additional two hour overtime.  In which case, he would click the Add a New Line item button and a new row would appear below monday , where the user can make the entries for overtime  As for the actual data that will populate the csv file.  That will come from columns O to AG.  The csv file is to be imported into a system that requires the columns in a certain order, along with several other columns that the user does not interact with.  So rather than exporting the columns as is from A15:N21, and then manipulating the csv file.  I used A15:N21 to populate O15 to AQ21 (which are in the correct order) and then just use that as the range for the csv file, hence the reason I test for the column number in the code and only process column numbers greater than 14

1) Will the format of the WTS sheets always be the same? In other words the data will always be for a week and it is shown in two sections the first in A14:N21 and the second in G26:M35
?

With respect to point 2, That is correct, if the value in column G is zero or blank I don't want that ROW in the csv file

2) Is it true that if a value in column G is zero or blank that you don't want any data for that day in the CSV

The csv file will only contain the data from columns O to AQ
0
Martin LissOlder than dirtCommented:
Since all the data comes from O to AQ can I use column 'S' to determine if the data should be in the CSV?

Also, and this may be a lot to ask, but as I asked in point 3, can you tell me by range address which cells and in which order they go into the CSV including the "heading" which looks like this in your posted CSV file.

12345
234

R
12345

L
8/26/2013
0
Juan VelasquezAuthor Commented:
Hello Martin,

Yes column "S" is the column to go by.  I'm am not including the headers in the csv.  As I go from O to AQ I write the contents into the csv file. row by row. , particularly trailing columns. Below is the code that I wrote to populate the range that I send to the RangeToCSV function that I am trying to get to work.

Private Sub GenerateOutputFile()
    ' Comments:
    ' Params  :
    ' Created : 08/13/13 14:42 JV
    ' Modified:
    
    On Error GoTo PROC_ERR
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim strFilter As String
    Dim rng As Range
    Dim OutputRange As Range
    Dim strJobType As String
    Dim strTmpCSV As String 'string to hold the CSV info
    Dim intF As Integer
    Dim cell As Range
    
    Set wb = ThisWorkbook
    Set ws = wb.ActiveSheet
    
    'strFilter = ahtAddFilterItem(strFilter, "CSV (*.csv)", "*.csv")
    'gstrFilePath = ahtCommonFileOpenSave(DialogTitle:="Save Output File", Filter:=strFilter, OpenFile:=False)
    'Kill gstrFilePath
    gstrFilePath = Environ$("temp") & "\OutputFile.csv"
    KillProperly (gstrFilePath)
    gstrFilePath = Environ$("temp") & "\OutputFile.csv"
    
    strJobType = wb.Worksheets("Summary").Range("JobType").value
    'MsgBox gstrFilePath
    
    For Each ws In wb.Worksheets
        ws.Unprotect
        If Left(ws.Name, 3) = "WTS" Then
            
            intF = FreeFile
             
            Open gstrFilePath For Append As #intF
            
            gstrEmployeeId = ws.Range("EmployeeID").value
            Application.EnableEvents = False
            
            'Set EmployeeId Range
            'Set r9 = ws.Range("rngWorkingDates")
            ws.Unprotect
            Set rng = ws.Range("WorkDate")
            For Each cell In rng.Cells
                If strJobType = "Construction" Then
                    cell.Offset(0, 14).value = cell.Offset(0, 8).value  'Batch code
                ElseIf strJobType = "Service" Then
                    cell.Offset(0, 14).value = gstrEmployeeId           'Batch code
                End If
                cell.Offset(0, 15).value = gstrEmployeeId           'Employee Code
                cell.Offset(0, 16).value = cell.Offset(0, 11).value 'Department
                cell.Offset(0, 17).value = cell.Offset(0, 7).value  'PayType
                cell.Offset(0, 18).value = cell.Offset(0, 6).value  'Hours
                cell.Offset(0, 19).value = cell.Offset(0, 8).value  'Job
                cell.Offset(0, 20).value = cell.Offset(0, 9).value  'Phase
                cell.Offset(0, 21).value = cell.Offset(0, 10).value 'Cost Type
                cell.Offset(0, 22).value = cell.Offset(0, 0).value  'Date
                cell.Offset(0, 23).value = cell.Offset(0, 13).value 'Message
                cell.Offset(0, 24).value = "'"                      'Payrate Level
                cell.Offset(0, 25).value = "'"                      'WageCode
                cell.Offset(0, 26).value = "'"                      'UnionCode
                cell.Offset(0, 27).value = "'"                      'WorkersComp
                cell.Offset(0, 28).value = "'"                      'StateTaxCode
                cell.Offset(0, 29).value = "'"                      'CountyTaxCode
                cell.Offset(0, 30).value = "'"                      'LocalTaxCode
                cell.Offset(0, 31).value = "'"                      'EquipmentCode
                cell.Offset(0, 32).value = "'"                      'EquipmentHours
                cell.Offset(0, 33).value = "'"                      'Qty
                cell.Offset(0, 34).value = "'"                      'CompanyCode
                cell.Offset(0, 35).value = "'"                      'PayRate
                cell.Offset(0, 36).value = "'"                      'EquipmentCostCategory
                cell.Offset(0, 37).value = "'"                      'CrewNumber
                cell.Offset(0, 38).value = cell.Offset(0, 12).value 'Cost Center
                cell.Offset(0, 39).value = cell.Offset(0, 2).value  'WorkOrder
                cell.Offset(0, 40).value = "'"                      'SiteEquipment
                cell.Offset(0, 41).value = "'"                      'SiteComponent
                cell.Offset(0, 42).value = "'"                      'Contract
                cell.Offset(0, 43).value = "'"                      'EquipmentWorkOrder
            Next
          
           
            Application.EnableEvents = True
           
            strTmpCSV = RangeToCSV(ws.Range("TimesheetEntry"))
          
            Print #intF, strTmpCSV
            Close #intF
            
            
        End If
       
    Next
   ' Kill gstrSavedWorkbookPath
    gstrSavedWorkbookPath = Environ$("temp") & "\Timesheets.xlsm"
    
    ThisWorkbook.SaveCopyAs FileName:=gstrSavedWorkbookPath
    'ThisWorkbook.SaveAs gstrSavedWorkbookPath, xlOpenXMLWorkbookMacroEnabled
    'If MsgBox("Do you wish to email the output file", vbYesNo, "Email Dialog") = vbYes Then
        frmEmailDialog.Show
    'End If
    
PROC_EXIT:
    Application.EnableEvents = True
    Exit Sub
    
PROC_ERR:
    MsgBox Err.Description, vbCritical, "Sheet1.GenerateOutputFile"
    Resume PROC_EXIT
    
End Sub

Open in new window

0
Martin LissOlder than dirtCommented:
If you have that code, then what is RangeToCSV all about?
0
Juan VelasquezAuthor Commented:
That how I create the CSV file.  The purpose of the above code is to provide the CSV file with a range having the columns in the correct order.  In addition, there are columns that are required but are not available in the user entry portion of the timesheet.  However, these columns need to be included in the csv file so that csv file can be imported into another system (it's called spectrum).  Once it hits the RangeToCSV, only those columns that are required to be imported into the spectrum system are loaded into the csv file.  This columns O to AQ
0
Martin LissOlder than dirtCommented:
Yes, sorry, I figured that out.
0
Juan VelasquezAuthor Commented:
Not a problem.  I find myself getting confused at times:)   I'm still working on it.  It seems there should be an easier way to do this.  Perhaps by reading in the populated csv file, looping through it and deleting rows where the HoursWorked (Column S in the spreadsheet) = 0
0
Martin LissOlder than dirtCommented:
Does the following work for you? Note the new lines 47 and 82. They stop the 0 or black time entries from being passed to RangeToCSV, and so you can then code RangeToCSV any way you want without worrying about invalid data.

Private Sub GenerateOutputFile()
    ' Comments:
    ' Params  :
    ' Created : 08/13/13 14:42 JV
    ' Modified:
    
    On Error GoTo PROC_ERR
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim strFilter As String
    Dim rng As Range
    Dim OutputRange As Range
    Dim strJobType As String
    Dim strTmpCSV As String 'string to hold the CSV info
    Dim intF As Integer
    Dim cell As Range
    
    Set wb = ThisWorkbook
    Set ws = wb.ActiveSheet
    
    'strFilter = ahtAddFilterItem(strFilter, "CSV (*.csv)", "*.csv")
    'gstrFilePath = ahtCommonFileOpenSave(DialogTitle:="Save Output File", Filter:=strFilter, OpenFile:=False)
    'Kill gstrFilePath
    gstrFilePath = Environ$("temp") & "\OutputFile.csv"
    KillProperly (gstrFilePath)
    gstrFilePath = Environ$("temp") & "\OutputFile.csv"
    
    strJobType = wb.Worksheets("Summary").Range("JobType").value
    'MsgBox gstrFilePath
    
    For Each ws In wb.Worksheets
        ws.Unprotect
        If Left(ws.Name, 3) = "WTS" Then
            
            intF = FreeFile
             
            Open gstrFilePath For Append As #intF
            
            gstrEmployeeId = ws.Range("EmployeeID").value
            Application.EnableEvents = False
            
            'Set EmployeeId Range
            'Set r9 = ws.Range("rngWorkingDates")
            ws.Unprotect
            Set rng = ws.Range("WorkDate")
            For Each cell In rng.Cells
                If cell.Offset(0, 6).value <> "0" And cell.Offset(0, 6).value <> "" Then
                    If strJobType = "Construction" Then
                        cell.Offset(0, 14).value = cell.Offset(0, 8).value  'Batch code
                    ElseIf strJobType = "Service" Then
                        cell.Offset(0, 14).value = gstrEmployeeId           'Batch code
                    End If
                    cell.Offset(0, 15).value = gstrEmployeeId           'Employee Code
                    cell.Offset(0, 16).value = cell.Offset(0, 11).value 'Department
                    cell.Offset(0, 17).value = cell.Offset(0, 7).value  'PayType
                    cell.Offset(0, 18).value = cell.Offset(0, 6).value  'Hours
                    cell.Offset(0, 19).value = cell.Offset(0, 8).value  'Job
                    cell.Offset(0, 20).value = cell.Offset(0, 9).value  'Phase
                    cell.Offset(0, 21).value = cell.Offset(0, 10).value 'Cost Type
                    cell.Offset(0, 22).value = cell.Offset(0, 0).value  'Date
                    cell.Offset(0, 23).value = cell.Offset(0, 13).value 'Message
                    cell.Offset(0, 24).value = "'"                      'Payrate Level
                    cell.Offset(0, 25).value = "'"                      'WageCode
                    cell.Offset(0, 26).value = "'"                      'UnionCode
                    cell.Offset(0, 27).value = "'"                      'WorkersComp
                    cell.Offset(0, 28).value = "'"                      'StateTaxCode
                    cell.Offset(0, 29).value = "'"                      'CountyTaxCode
                    cell.Offset(0, 30).value = "'"                      'LocalTaxCode
                    cell.Offset(0, 31).value = "'"                      'EquipmentCode
                    cell.Offset(0, 32).value = "'"                      'EquipmentHours
                    cell.Offset(0, 33).value = "'"                      'Qty
                    cell.Offset(0, 34).value = "'"                      'CompanyCode
                    cell.Offset(0, 35).value = "'"                      'PayRate
                    cell.Offset(0, 36).value = "'"                      'EquipmentCostCategory
                    cell.Offset(0, 37).value = "'"                      'CrewNumber
                    cell.Offset(0, 38).value = cell.Offset(0, 12).value 'Cost Center
                    cell.Offset(0, 39).value = cell.Offset(0, 2).value  'WorkOrder
                    cell.Offset(0, 40).value = "'"                      'SiteEquipment
                    cell.Offset(0, 41).value = "'"                      'SiteComponent
                    cell.Offset(0, 42).value = "'"                      'Contract
                    cell.Offset(0, 43).value = "'"                      'EquipmentWorkOrder
                End If
            Next
          
           
            Application.EnableEvents = True
           
            strTmpCSV = RangeToCSV(ws.Range("TimesheetEntry"))
          
            Print #intF, strTmpCSV
            Close #intF
            
            
        End If
       
    Next
   ' Kill gstrSavedWorkbookPath
    gstrSavedWorkbookPath = Environ$("temp") & "\Timesheets.xlsm"
    
    ThisWorkbook.SaveCopyAs FileName:=gstrSavedWorkbookPath
    'ThisWorkbook.SaveAs gstrSavedWorkbookPath, xlOpenXMLWorkbookMacroEnabled
    'If MsgBox("Do you wish to email the output file", vbYesNo, "Email Dialog") = vbYes Then
        frmEmailDialog.Show
    'End If
    
PROC_EXIT:
    Application.EnableEvents = True
    Exit Sub
    
PROC_ERR:
    MsgBox Err.Description, vbCritical, "Sheet1.GenerateOutputFile"
    Resume PROC_EXIT
    
End Sub

Open in new window

0
Martin LissOlder than dirtCommented:
Well, I'm embarrassed since my conclusion is wrong. You are still passing the whole range.
0
Juan VelasquezAuthor Commented:
I just tested it with the new code, and you're right, the entire range is still being passed
0
Martin LissOlder than dirtCommented:
I tried to send the output csv file to myself by entering my email address but I never received anything, so try this. In my code above at line 47, instead of just checking for 0 or blank, add code that filters or hides those rows. I'm not sure about hiding them but if I remember correctly, filtering them out will cause them not to be in the range.
0
Juan VelasquezAuthor Commented:
As a test I tried manually filtering the timesheets and reran the code and the whole range was still passed
0
Martin LissOlder than dirtCommented:
Alright this is my swan song since I find working with the rng object too confusing. This is the outline of another way to generate the csv file without the rng object.


Private Sub AnotherWay()
    Dim ws As Worksheet
    Dim lngLastRow As Long
    Dim lngFirstRow As Long
    Dim lngRow As Long
    
    For Each ws In Worksheets
        If Left(ws.Name, 3) = "WTS" Then
            ' The 1st section
            lngLastRow = Range("A65536").End(xlUp).row
            lngFirstRow = 15
            For lngRow = lngFirstRow To lngLastRow
                If Cells(lngRow, 7) <> 0 And Cells(lngRow, 7) <> 0 Then
                    ' write the data to the csv file
                End If
            Next
            ' the second section - only needed if you actually get any info for the csv from here
            For lngRow = lngLastRow To lngLastRow + 20
                If Cells(lngRow, 7) = "Date" Then
                    lngFirstRow = lngRow + 1
                    Exit For
                End If
            Next
            lngLastRow = Range("G65536").End(xlUp).row
            For lngRow = lngFirstRow To lngLastRow
                If Cells(lngRow, 8) <> 0 And Cells(lngRow, 8) <> 0 Then
                    ' write the data to the csv file
                End If
            Next

        End If
    Next
    
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Juan VelasquezAuthor Commented:
Hello Martin,

I have to agree.  I came with a possible solution, but it was a messy and difficult to maintain.  I'm going try your approach. After I populate columnsO to AQ, I'll loop through the worksheets again and append the rows to the csv file.  

Private Sub GenerateOutputFile()
    ' Comments:
    ' Params  :
    ' Created : 08/13/13 14:42 JV
    ' Modified:
    
    On Error GoTo PROC_ERR
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim strFilter As String
    Dim rng As Range
    Dim OutputRange As Range
    Dim strJobType As String
    Dim strTmpCSV As String 'string to hold the CSV info
    Dim intF As Integer
    Dim cell As Range
    
    Set wb = ThisWorkbook
    Set ws = wb.ActiveSheet
 
    gstrFilePath = Environ$("temp") & "\OutputFile.csv"
    KillProperly (gstrFilePath)
    gstrFilePath = Environ$("temp") & "\OutputFile.csv"
    
    strJobType = wb.Worksheets("Summary").Range("JobType").value
    
    
    For Each ws In wb.Worksheets
        ws.Unprotect
        If Left(ws.Name, 3) = "WTS" Then
            
            intF = FreeFile
             
           
            
            gstrEmployeeId = ws.Range("EmployeeID").value
            Application.EnableEvents = False
            
            ws.Unprotect
            Set rng = ws.Range("WorkDate")
            For Each cell In rng.Cells
                If strJobType = "Construction" Then
                    cell.Offset(0, 14).value = cell.Offset(0, 8).value  'Batch code
                ElseIf strJobType = "Service" Then
                    cell.Offset(0, 14).value = gstrEmployeeId           'Batch code
                End If
                cell.Offset(0, 15).value = gstrEmployeeId           'Employee Code
                cell.Offset(0, 16).value = cell.Offset(0, 11).value 'Department
                cell.Offset(0, 17).value = cell.Offset(0, 7).value  'PayType
                cell.Offset(0, 18).value = cell.Offset(0, 6).value  'Hours
                cell.Offset(0, 19).value = cell.Offset(0, 8).value  'Job
                cell.Offset(0, 20).value = cell.Offset(0, 9).value  'Phase
                cell.Offset(0, 21).value = cell.Offset(0, 10).value 'Cost Type
                cell.Offset(0, 22).value = cell.Offset(0, 0).value  'Date
                cell.Offset(0, 23).value = cell.Offset(0, 13).value 'Message
                cell.Offset(0, 24).value = "'"                      'Payrate Level
                cell.Offset(0, 25).value = "'"                      'WageCode
                cell.Offset(0, 26).value = "'"                      'UnionCode
                cell.Offset(0, 27).value = "'"                      'WorkersComp
                cell.Offset(0, 28).value = "'"                      'StateTaxCode
                cell.Offset(0, 29).value = "'"                      'CountyTaxCode
                cell.Offset(0, 30).value = "'"                      'LocalTaxCode
                cell.Offset(0, 31).value = "'"                      'EquipmentCode
                cell.Offset(0, 32).value = "'"                      'EquipmentHours
                cell.Offset(0, 33).value = "'"                      'Qty
                cell.Offset(0, 34).value = "'"                      'CompanyCode
                cell.Offset(0, 35).value = "'"                      'PayRate
                cell.Offset(0, 36).value = "'"                      'EquipmentCostCategory
                cell.Offset(0, 37).value = "'"                      'CrewNumber
                cell.Offset(0, 38).value = cell.Offset(0, 12).value 'Cost Center
                cell.Offset(0, 39).value = cell.Offset(0, 2).value  'WorkOrder
                cell.Offset(0, 40).value = "'"                      'SiteEquipment
                cell.Offset(0, 41).value = "'"                      'SiteComponent
                cell.Offset(0, 42).value = "'"                      'Contract
                cell.Offset(0, 43).value = "'"                      'EquipmentWorkOrder
            Next
          
           
            Application.EnableEvents = True
           
            
            
        End If
       
    Next
    
    
    Dim lngLastRow As Long
    Dim lngFirstRow As Long
    Dim lngRow As Long
    
    For Each ws In Worksheets
        If Left(ws.Name, 3) = "WTS" Then
            intF = FreeFile
             
            Open gstrFilePath For Append As #intF
            ' The 1st section
            lngLastRow = Range("A65536").End(xlUp).row
            lngFirstRow = 15
            For lngRow = lngFirstRow To lngLastRow
                If Cells(lngRow, 7) <> 0 And Cells(lngRow, 7) <> 0 Then
                    Open gstrFilePath For Append As #intF
                  ' strTmpCSV = RangeToCSV(ws.Range())
                    Print #intF, strTmpCSV
                    Close #intF
                End If
            Next
            ' the second section - only needed if you actually get any info for the csv from here
            For lngRow = lngLastRow To lngLastRow + 20
                If Cells(lngRow, 7) = "Date" Then
                    lngFirstRow = lngRow + 1
                    Exit For
                End If
            Next
            lngLastRow = Range("G65536").End(xlUp).row
            For lngRow = lngFirstRow To lngLastRow
                If Cells(lngRow, 8) <> 0 And Cells(lngRow, 8) <> 0 Then
                    Open gstrFilePath For Append As #intF
                  ' strTmpCSV = RangeToCSV(ws.Range())
                    Print #intF, strTmpCSV
                    Close #intF
                End If
            Next

        End If
    Next
    
     
            
          
          
    
    
    gstrSavedWorkbookPath = Environ$("temp") & "\Timesheets.xlsm"
    
    ThisWorkbook.SaveCopyAs FileName:=gstrSavedWorkbookPath
  
        frmEmailDialog.Show
    
    
PROC_EXIT:
    Application.EnableEvents = True
    Exit Sub
    
PROC_ERR:
    MsgBox Err.Description, vbCritical, "Sheet1.GenerateOutputFile"
    Resume PROC_EXIT
    

Open in new window

0
Juan VelasquezAuthor Commented:
Hello Martin,

I've modified the code as shown below.  It looks like the range is being passed to RangeToCSV but I need to modify that function slightly as I am not populating strTmp

Private Sub GenerateOutputFile()
    ' Comments:
    ' Params  :
    ' Created : 08/13/13 14:42 JV
    ' Modified:
    
    On Error GoTo PROC_ERR
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim strFilter As String
    Dim rng As Range
    Dim OutputRange As Range
    Dim strJobType As String
    Dim strTmpCSV As String 'string to hold the CSV info
    Dim intF As Integer
    Dim cell As Range
   
    
    Set wb = ThisWorkbook
    Set ws = wb.ActiveSheet
 
    gstrFilePath = Environ$("temp") & "\OutputFile.csv"
    KillProperly (gstrFilePath)
    gstrFilePath = Environ$("temp") & "\OutputFile.csv"
    
    strJobType = wb.Worksheets("Summary").Range("JobType").value
    
    
    For Each ws In wb.Worksheets
        ws.Unprotect
        If Left(ws.Name, 3) = "WTS" Then
            
            'intF = FreeFile
             
           
            
            gstrEmployeeId = ws.Range("EmployeeID").value
            Application.EnableEvents = False
            
            ws.Unprotect
            Set rng = ws.Range("WorkDate")
            For Each cell In rng.Cells
                If strJobType = "Construction" Then
                    cell.Offset(0, 14).value = cell.Offset(0, 8).value  'Batch code
                ElseIf strJobType = "Service" Then
                    cell.Offset(0, 14).value = gstrEmployeeId           'Batch code
                End If
                cell.Offset(0, 15).value = gstrEmployeeId           'Employee Code
                cell.Offset(0, 16).value = cell.Offset(0, 11).value 'Department
                cell.Offset(0, 17).value = cell.Offset(0, 7).value  'PayType
                cell.Offset(0, 18).value = cell.Offset(0, 6).value  'Hours
                cell.Offset(0, 19).value = cell.Offset(0, 8).value  'Job
                cell.Offset(0, 20).value = cell.Offset(0, 9).value  'Phase
                cell.Offset(0, 21).value = cell.Offset(0, 10).value 'Cost Type
                cell.Offset(0, 22).value = cell.Offset(0, 0).value  'Date
                cell.Offset(0, 23).value = cell.Offset(0, 13).value 'Message
                cell.Offset(0, 24).value = "'"                      'Payrate Level
                cell.Offset(0, 25).value = "'"                      'WageCode
                cell.Offset(0, 26).value = "'"                      'UnionCode
                cell.Offset(0, 27).value = "'"                      'WorkersComp
                cell.Offset(0, 28).value = "'"                      'StateTaxCode
                cell.Offset(0, 29).value = "'"                      'CountyTaxCode
                cell.Offset(0, 30).value = "'"                      'LocalTaxCode
                cell.Offset(0, 31).value = "'"                      'EquipmentCode
                cell.Offset(0, 32).value = "'"                      'EquipmentHours
                cell.Offset(0, 33).value = "'"                      'Qty
                cell.Offset(0, 34).value = "'"                      'CompanyCode
                cell.Offset(0, 35).value = "'"                      'PayRate
                cell.Offset(0, 36).value = "'"                      'EquipmentCostCategory
                cell.Offset(0, 37).value = "'"                      'CrewNumber
                cell.Offset(0, 38).value = cell.Offset(0, 12).value 'Cost Center
                cell.Offset(0, 39).value = cell.Offset(0, 2).value  'WorkOrder
                cell.Offset(0, 40).value = "'"                      'SiteEquipment
                cell.Offset(0, 41).value = "'"                      'SiteComponent
                cell.Offset(0, 42).value = "'"                      'Contract
                cell.Offset(0, 43).value = "'"                      'EquipmentWorkOrder
            Next
          
           
            Application.EnableEvents = True
           
            
            
        End If
       
    Next
    
    
    Dim lngLastRow As Long
    Dim lngFirstRow As Long
    Dim lngRow As Long
    
    For Each ws In Worksheets
        If Left(ws.Name, 3) = "WTS" Then
            intF = FreeFile
             
            'Open gstrFilePath For Append As #intF
            ' The 1st section
            'lngLastRow = Range("WorkDate").End(xlUp).row
            lngLastRow = ws.Range("WorkDate").Cells(1, 1).row + ws.Range("WorkDate").Rows.Count - 1
            lngFirstRow = 15
            For lngRow = lngFirstRow To lngLastRow
                If Cells(lngRow, 7).value <> 0 And Cells(lngRow, 7).value <> 0 Then
                    Open gstrFilePath For Append As #intF
                    
                    Set rng = Range(Cells(lngRow, 1), Cells(lngRow, 43))
                   
                    strTmpCSV = RangeToCSV(rng)
                    Print #intF, strTmpCSV
                    Close #intF
                End If
            Next
            ' the second section - only needed if you actually get any info for the csv from here
'            For lngRow = lngLastRow To lngLastRow + 20
'                If Cells(lngRow, 7) = "Date" Then
'                    lngFirstRow = lngRow + 1
'                    Exit For
'                End If
'            Next
'            lngLastRow = Range("G65536").End(xlUp).row
'            For lngRow = lngFirstRow To lngLastRow
'                If Cells(lngRow, 8) <> 0 And Cells(lngRow, 8) <> 0 Then
'                    Open gstrFilePath For Append As #intF
'                  ' strTmpCSV = RangeToCSV(ws.Range())
'                    Print #intF, strTmpCSV
'                    Close #intF
'                End If
'            Next

        End If
    Next
    
     
            
          
          
    
    
    gstrSavedWorkbookPath = Environ$("temp") & "\Timesheets.xlsm"
    
    ThisWorkbook.SaveCopyAs FileName:=gstrSavedWorkbookPath
  
        frmEmailDialog.Show
    
    
PROC_EXIT:
    Application.EnableEvents = True
    Exit Sub
    
PROC_ERR:
    MsgBox Err.Description, vbCritical, "Sheet1.GenerateOutputFile"
    Resume PROC_EXIT
    
End Sub


Public Function RangeToCSV(list As Range) As String
    ' Comments:
    ' Params  : list
    ' Returns : String
    ' Modified:

    'TVCodeTools ErrorEnablerStart
    On Error GoTo PROC_ERR
    'TVCodeTools ErrorEnablerEnd

    Dim strTmp As String
    Dim lngCurrentRow As Long
    Dim lngCurrentColumn As Long
    Dim rng As Range
    Dim xlsRange As Range


    If TypeName(list) = "Range" Then
        lngCurrentRow = 15
            For Each rng In list.Cells
                If Range(rng.Address).Column > 14 Then
                    If rng.row = lngCurrentRow Then
                        If strTmp = vbNullString Then
                            strTmp = rng.value
                        Else
                            strTmp = strTmp & "," & rng.value
                        End If
                    Else
                        lngCurrentRow = lngCurrentRow + 1
                        If strTmp = vbNullString Then
                            strTmp = rng.value
                        Else
                            strTmp = strTmp & Chr(10) & rng.value
                        End If
                    End If
                End If
             
            Next
    End If

    RangeToCSV = strTmp

    'TVCodeTools ErrorHandlerStart
PROC_EXIT:
    Application.EnableEvents = True
    Exit Function

PROC_ERR:
    MsgBox Err.Description, vbCritical, "Sheet1.RangeToCSV"
    Resume PROC_EXIT
    'TVCodeTools ErrorHandlerEnd

Open in new window

0
Juan VelasquezAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for chtullu135's comment #a39451427

for the following reason:

Thanks for all your help
0
Juan VelasquezAuthor Commented:
I mean't to assign the point to martin
0
Juan VelasquezAuthor Commented:
I meant to assign the points to Martin
0
Juan VelasquezAuthor Commented:
Thanks again for your help
0
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2013
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.