Error 13 when trying to store pivot table cell's value as long-CodePudding

The purpose of this code is to run through several pivot tables, each in its own sheet, and check to see, for each row, if the last cell is lower than 20% of the average of the others, and if it is color it yellow.

Dim ctr, ctr2, ctr3 As Integer
Dim vlu, vlu2, vlu3 As Long

For ctr3 = 3 To wb.Worksheets.Count Step 2
    For ctr = 3 To lr(new_ws, 1)
        vlu = 0
        For ctr2 = 1 To lc(new_ws, 2) - 1
            If wb.Worksheets(ctr3).Cells(ctr, ctr2).Value <> Empty Then
                vlu3 = wb.Worksheets(ctr3).Cells(ctr, ctr2).Value         %%% ERROR 13 HERE %%%
                vlu = vlu   vlu3
            End If
        Next ctr2
        vlu2 = wb.Worksheets(ctr3).Cells(ctr, lc(wb.Worksheets(ctr3), 2)).Value
        vlu = (vlu / lc(wb.Worksheets(ctr3), 2) - 1) - (vlu * 0.2)
        If vlu2 < vlu Then
            'wb.Worksheets(ctr3).Cells(ctr, lc(wb.Worksheets(ctr3), 2)).Interior.ColorIndex = 6
        End If
    Next ctr
Next ctr3

CodePudding user response：

You can try to convert value in Long

vlu3=CLng(wb.Worksheets(ctr3).Cells(ctr, ctr2).Value)

CodePudding user response：

The issue was that not all values were numeric, since there were strings values dotted amongst the data.

By adding the line If IsNumeric(wb.Worksheets(ctr3).Cells(ctr, ctr2))= True Then before the line that gave the error, I was able to fix the issue.