troubleshooting Question

Need help on a for loop

Avatar of Juan Velasquez
Juan VelasquezFlag for United States of America asked on
Microsoft OfficeMicrosoft ExcelMicrosoft Applications
32 Comments1 Solution378 ViewsLast Modified:
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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 32 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 32 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros