CODEWITHSUNDEEP
vba
user6156963
user6156963

Reputation:

VBA Run Time Error 13 Type Mismatch #VALUE

In some of my column's cells there appear #VALUE! words and formulas inside are as follows example:

=IF(VALUE(RIGHT(CELL("nome.arquivo";A1);LEN(CELL("nome.arquivo";A1))-SEARCH("]";CELL("nome.arquivo";A1))))=1;1;1+INDIRECT(ADDRESS(329;COLUMN();;;VALUE(RIGHT(CELL("nome.arquivo";A1);LEN(CELL("nome.arquivo";A1))-SEARCH("]";CELL("nome.arquivo";A1))))-1)))

Mentioned column is CT. Now when i am trying to loop through the cells when it comes to first occurence of #VALUE! i get an error:

Run Time Error 13, Type mismatch on this line:

 L = 9
         Do While Cells(L, "CT").Value <> ""         '<========= HERE AN ERROR
            L = L + 8
         Loop

How to fix that?

Upvotes: 0

Views: 490

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

Cells doesn't take any String parameter, the type mismatch error you're getting has nothing to do with #VALUE / the data being processed - because the code never gets to evaluate the data, since Cells wants two Integer parameters: dang this bites me everytime - apparently you can use string parameters in the Cells function. Ugh.

So the problem is with comparing the value to "" - a string. #VALUE! isn't a valid string function so you need to account for it:

Dim theCell As Range
Set theCell = Cells(L, "CT")
If Not IsError(theCell.Value) Then
    Do While Not IsEmpty(theCell.Value)
        '...
        L = L + 8
    Loop
End If

You might also want to correctly qualify that function call:

Do While Not IsEmpty(ActiveSheet.Cells(L, "CT").Value)

That way it's explicit that you're looking at a cell in the active worksheet; an unqualified call is implicitly doing that, and anything implicit is potentially confusing and bug-prone.

Make sure the top of the module says Option Explicit and that L is properly declared:

Dim L As Long

"L" being a meaningless identifier, you should consider naming it after what you're using it for:

Dim currentRow As Long
currentRow = 9

Do While Not IsEmpty(ActiveSheet.Cells(currentRow, "CT"))

That way your code becomes much easier to read, follow and understand.

Upvotes: 1

Related Questions