Excel VBA Variable is not incrementing correctly

Question

I have the below Excel 2016 VBA function where I am outputting data to various worksheets. I seem to be having an issue with the data on my "Error" worksheet where I am getting a lot of blank rows between other rows with data.

This issue is only occuring with the "Error" worksheet. I have tried putting the k = k + 1 variable around some and it has not helped. Is there any obvious place where I should (or shouldn't be) incrementing k that may fix the issue? Or perhaps l and j need to be changed?

Function PPDdate()

    Dim PPD_1_Date As Date
    Dim PPD_2_Date As Date
    Dim TSpot_Date As Variant
    Dim i As Long, j As Long, k As Long

    j = Worksheets("PPDCI").Range("A" & Rows.Count).End(xlUp).Row + 1
    l = Worksheets("CI").Range("A" & Rows.Count).End(xlUp).Row + 1
    k = Worksheets("Error").Range("A" & Rows.Count).End(xlUp).Row + 1

    For i = 2 To lstrow

        PPD_1_Date = Worksheets("Data").Range("AW" & i)
        PPD_2_Date = Worksheets("Data").Range("BA" & i)
        Entity = Worksheets("Data").Range("J" & i)
        Dept = Worksheets("Data").Range("M" & i)
        TSpot_Date = Worksheets("Data").Range("AS" & i)


        If PPD_1_Date > PPD_2_Date Then
            Worksheets("PPDCI").Range("A" & j & ":C" & j).Value = Worksheets("Data").Range("A" & i & ":C" & i).Value
            Worksheets("PPDCI").Range("F" & j).Value = PPD_1_Date
            Worksheets("PPDCI").Range("G" & j).Value = Worksheets("Data").Range("AX" & i).Value
            Worksheets("PPDCI").Range("H" & j).Value = Worksheets("Data").Range("AZ" & i).Value
            Worksheets("PPDCI").Range("I" & j).Value = Worksheets("Data").Range("AY" & i).Value
            Worksheets("PPDCI").Range("J" & j).Value = "1st IF STATEMENT"
            j = j + 1
        Else
            If PPD_1_Date < PPD_2_Date Then
                Worksheets("PPDCI").Range("A" & j & ":C" & j).Value = Worksheets("Data").Range("A" & i & ":C" & i).Value
                Worksheets("PPDCI").Range("F" & j).Value = PPD_2_Date
                'Worksheets("PPDCI").Range("G" & j).Value = "ELSE IF CONDITION"
                Worksheets("PPDCI").Range("G" & j).Value = Worksheets("Data").Range("BB" & i).Value
                Worksheets("PPDCI").Range("H" & j).Value = Worksheets("Data").Range("BD" & i).Value
                Worksheets("PPDCI").Range("I" & j).Value = Worksheets("Data").Range("BC" & i).Value
                Worksheets("PPDCI").Range("J" & j).Value = "2nd IF STATEMENT"
                j = j + 1

                'If IsEmpty(Worksheets("Data").Range(PPD_1_Date & i).Value) = True And IsEmpty(Worksheets("Data").Range(PPD_2_Date & i).Value) = True Then
                'GoTo EmptyRange
                'Else

            Else
                If Len(TSpot_Date) <> 0 And InStr(1, UCase(Worksheets("Data").Range("AT" & i).Value), "NEG") > 0 Then
                    Worksheets("CI").Range("A" & j & ":C" & j).Value = Worksheets("Data").Range("A" & i & ":C" & i).Value
                    Worksheets("CI").Range("D" & j).Value = "TSNG"
                    Worksheets("CI").Range("E" & j).Value = TSpot_Date
                    Worksheets("CI").Range("F" & j).Value = "3rd IF STATEMENT"
                    'j = j + 1

                Else
                    If Len(TSpot_Date) <> 0 And InStr(1, UCase(Worksheets("Data").Range("AT" & i).Value), "POS") > 0 Then
                        Worksheets("CI").Range("A" & j & ":C" & j).Value = Worksheets("Data").Range("A" & i & ":C" & i).Value
                        Worksheets("CI").Range("D" & j).Value = "TSPS"
                        Worksheets("CI").Range("E" & j).Value = TSpot_Date
                        Worksheets("CI").Range("F" & j).Value = "4th IF STATEMENT"
                        l = l + 1

                    Else
                        If (InStr(1, Entity, "CNG Hospital") > 0 Or InStr(1, Entity, "Home Health") > 0 Or InStr(1, Entity, "Hospice") > 0 Or InStr(1, Dept, "Volunteers") > 0 And (PPD_1_Date = 0 And PPD_2_Date = 0)) And TSpot_Date = 0 Then
                            Worksheets("Error").Range("A" & k & ":H" & k).Value = Worksheets("Data").Range("A" & i & ":C" & i).Value
                            Worksheets("Error").Range("F" & k).Value = "REVIEW PPD DATA"
                            Worksheets("Error").Range("G" & k).Value = "5th IF STATEMENT"
                            k = k + 1

                        Else
                            Worksheets("Error").Range("A" & j & ":C" & j).Value = Worksheets("Data").Range("A" & i & ":C" & i).Value
                            Worksheets("Error").Range("D" & j).Value = TSpot_Date
                            Worksheets("Error").Range("E" & j).Value = Worksheets("Data").Range("AT" & i).Value
                            Worksheets("Error").Range("F" & j).Value = "REVIEW PPD/TSPOT DATA"
                            Worksheets("Error").Range("G" & j).Value = "6th IF STATEMENT"
                            'k = k + 1

                        End If

                    End If

                End If

            End If

        End If

'EmptyRange:
        'k = k + 1

    Next i

End Function

Example output:

Thanks in advance!

This would look a lot better if you changed the nested IFs to ElseIfs... I would think anyways. Have you stepped through your code to see if the area you are copying is blank? — seadoggie01
– seadoggie01, Commented Sep 27, 2018 at 18:10
lstrow is defined as: lstrow = Worksheets("Data").Range("A" & Rows.Count).End(xlUp).Row — Nick
– Nick, Commented Sep 27, 2018 at 18:21
lstrow isn't defined in your code, so this isn't a minimal reproducible example. Also, you have a number of undeclared variables. Step one in debugging is to use Option Explicit and fix the resulting compile errors. — John Coleman
– John Coleman, Commented Sep 27, 2018 at 18:28

learnAsWeGo · Accepted Answer · 2018-09-27 18:26:46Z

You are setting the range in the else statement of your print to ERROR sheet with J when it should be K

                Else
                    If (InStr(1, Entity, "CNG Hospital") > 0 Or InStr(1, Entity, "Home Health") > 0 Or InStr(1, Entity, "Hospice") > 0 Or InStr(1, Dept, "Volunteers") > 0 And (PPD_1_Date = 0 And PPD_2_Date = 0)) And TSpot_Date = 0 Then
                        Worksheets("Error").Range("A" & k & ":H" & k).Value = Worksheets("Data").Range("A" & i & ":C" & i).Value
                        Worksheets("Error").Range("F" & k).Value = "REVIEW PPD DATA"
                        Worksheets("Error").Range("G" & k).Value = "5th IF STATEMENT"
                        k = k + 1

                    Else
                        Worksheets("Error").Range("A" & j & ":C" & j).Value = Worksheets("Data").Range("A" & i & ":C" & i).Value
                        Worksheets("Error").Range("D" & j).Value = TSpot_Date
                        Worksheets("Error").Range("E" & j).Value = Worksheets("Data").Range("AT" & i).Value
                        Worksheets("Error").Range("F" & j).Value = "REVIEW PPD/TSPOT DATA"
                        Worksheets("Error").Range("G" & j).Value = "6th IF STATEMENT"
                        k = k + 1

                    End If

Should be

            Else
                If (InStr(1, Entity, "CNG Hospital") > 0 Or InStr(1, Entity, "Home Health") > 0 Or InStr(1, Entity, "Hospice") > 0 Or InStr(1, Dept, "Volunteers") > 0 And (PPD_1_Date = 0 And PPD_2_Date = 0)) And TSpot_Date = 0 Then
                    Worksheets("Error").Range("A" & k & ":H" & k).Value = Worksheets("Data").Range("A" & i & ":C" & i).Value
                    Worksheets("Error").Range("F" & k).Value = "REVIEW PPD DATA"
                    Worksheets("Error").Range("G" & k).Value = "5th IF STATEMENT"
                    k = k + 1

                Else
                    Worksheets("Error").Range("A" & k & ":C" & k).Value = Worksheets("Data").Range("A" & i & ":C" & i).Value
                    Worksheets("Error").Range("D" & k).Value = TSpot_Date
                    Worksheets("Error").Range("E" & k).Value = Worksheets("Data").Range("AT" & i).Value
                    Worksheets("Error").Range("F" & k).Value = "REVIEW PPD/TSPOT DATA"
                    Worksheets("Error").Range("G" & k).Value = "6th IF STATEMENT"
                    k = k + 1
                End If

I think that same issue can be found in your print to CI else if statement.

Also, as one of the comments states, how are you setting your last row?

You should consider declaring your worksheets, would make this cleaner IMO

Yes that was the issue, I switched around this conditional and overlooked changing the range. This now appears to be working as expected, thanks very much!
@Nick would this bug have happened if variable k was named errorRow? #FoodForThought

Mathieu Guindon · Accepted Answer · 2018-09-27 19:09:00Z

As was noted in other answers, & j should have been & k. However that isn't the "root issue".

The "root issue" is poor variable naming. Take the time to name things properly, it's worth it.

Rename i to currentRowData
Rename j to currentRowPPDCI
Rename k to currentRowError
Rename l to currentRowCI

Writing code is 5% of the job. Reading code is the other 95%. Therefore, code should be written to be read, not just executed. Yes, using meaningful identifiers take - oh, a whole second more to type. But they also make the code easier to read, which makes bugs like these much easier to catch.

i is a fine identifier when you're looking at a trivial For...Next loop. Anything else deserves a real name.

l is arguably the single most horrible single-letter identifier to use, because it's easily mistaken for a 1 at a glance.

oxwilder · Accepted Answer · 2018-09-27 18:33:19Z

0

It looks to me like your final "else" clause is using the last row of the "PPDCI" worksheet instead of the "errors" worksheet--the variable j instead of k.

answered Sep 27, 2018 at 18:33

oxwilder

7775 silver badges15 bronze badges

Comments

cybernetic.nomad · Accepted Answer · 2018-09-27 18:40:26Z

You are not consistent with the use of your row counters (j, k and l) in relation to your sheets (PPDCI, CI and Error)

try:

Function PPDdate()

    Dim PPD_1_Date As Date
    Dim PPD_2_Date As Date
    Dim TSpot_Date As Variant
    Dim i As Long, j As Long, k As Long

    j = Worksheets("PPDCI").Range("A" & Rows.Count).End(xlUp).Row + 1
    l = Worksheets("CI").Range("A" & Rows.Count).End(xlUp).Row + 1
    k = Worksheets("Error").Range("A" & Rows.Count).End(xlUp).Row + 1

    For i = 2 To lstrow

        PPD_1_Date = Worksheets("Data").Range("AW" & i)
        PPD_2_Date = Worksheets("Data").Range("BA" & i)
        Entity = Worksheets("Data").Range("J" & i)
        Dept = Worksheets("Data").Range("M" & i)
        TSpot_Date = Worksheets("Data").Range("AS" & i)


        If PPD_1_Date > PPD_2_Date Then
            Worksheets("PPDCI").Range("A" & j & ":C" & j).Value = Worksheets("Data").Range("A" & i & ":C" & i).Value
            Worksheets("PPDCI").Range("F" & j).Value = PPD_1_Date
            Worksheets("PPDCI").Range("G" & j).Value = Worksheets("Data").Range("AX" & i).Value
            Worksheets("PPDCI").Range("H" & j).Value = Worksheets("Data").Range("AZ" & i).Value
            Worksheets("PPDCI").Range("I" & j).Value = Worksheets("Data").Range("AY" & i).Value
            Worksheets("PPDCI").Range("J" & j).Value = "1st IF STATEMENT"
            j = j + 1
        Else
            If PPD_1_Date < PPD_2_Date Then
                Worksheets("PPDCI").Range("A" & j & ":C" & j).Value = Worksheets("Data").Range("A" & i & ":C" & i).Value
                Worksheets("PPDCI").Range("F" & j).Value = PPD_2_Date
                'Worksheets("PPDCI").Range("G" & j).Value = "ELSE IF CONDITION"
                Worksheets("PPDCI").Range("G" & j).Value = Worksheets("Data").Range("BB" & i).Value
                Worksheets("PPDCI").Range("H" & j).Value = Worksheets("Data").Range("BD" & i).Value
                Worksheets("PPDCI").Range("I" & j).Value = Worksheets("Data").Range("BC" & i).Value
                Worksheets("PPDCI").Range("J" & j).Value = "2nd IF STATEMENT"
                j = j + 1
            Else
                If Len(TSpot_Date) <> 0 And InStr(1, UCase(Worksheets("Data").Range("AT" & i).Value), "NEG") > 0 Then
                    Worksheets("CI").Range("A" & l & ":C" & l).Value = Worksheets("Data").Range("A" & i & ":C" & i).Value
                    Worksheets("CI").Range("D" & l).Value = "TSNG"
                    Worksheets("CI").Range("E" & l).Value = TSpot_Date
                    Worksheets("CI").Range("F" & l).Value = "3rd IF STATEMENT"
                    l = l + 1
                Else
                    If Len(TSpot_Date) <> 0 And InStr(1, UCase(Worksheets("Data").Range("AT" & i).Value), "POS") > 0 Then
                        Worksheets("CI").Range("A" & l & ":C" & l).Value = Worksheets("Data").Range("A" & i & ":C" & i).Value
                        Worksheets("CI").Range("D" & l).Value = "TSPS"
                        Worksheets("CI").Range("E" & l).Value = TSpot_Date
                        Worksheets("CI").Range("F" & l).Value = "4th IF STATEMENT"
                        l = l + 1
                    Else
                        If (InStr(1, Entity, "CNG Hospital") > 0 Or InStr(1, Entity, "Home Health") > 0 Or InStr(1, Entity, "Hospice") > 0 Or InStr(1, Dept, "Volunteers") > 0 And (PPD_1_Date = 0 And PPD_2_Date = 0)) And TSpot_Date = 0 Then
                            Worksheets("Error").Range("A" & k & ":H" & k).Value = Worksheets("Data").Range("A" & i & ":C" & i).Value
                            Worksheets("Error").Range("F" & k).Value = "REVIEW PPD DATA"
                            Worksheets("Error").Range("G" & k).Value = "5th IF STATEMENT"
                            k = k + 1
                        Else
                            Worksheets("Error").Range("A" & k & ":C" & k).Value = Worksheets("Data").Range("A" & i & ":C" & i).Value
                            Worksheets("Error").Range("D" & k).Value = TSpot_Date
                            Worksheets("Error").Range("E" & k).Value = Worksheets("Data").Range("AT" & i).Value
                            Worksheets("Error").Range("F" & k).Value = "REVIEW PPD/TSPOT DATA"
                            Worksheets("Error").Range("G" & k).Value = "6th IF STATEMENT"

                        End If
                    End If
                End If
            End If
        End If
    Next i

End Function

Collectives™ on Stack Overflow

Excel VBA Variable is not incrementing correctly

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related