asked on

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

Martin Liss

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