Compiling error :invalid qualifier when using an array to store not matching data within a loop

Question

I've written a code aimed to store in certain cells of worksheet(1) values of column 3 of worksheet(2) that doesn't match within a certain range of worksheet(3), the code is the following:

    Sub storedata()

Dim i As Integer
Dim n As Integer
Dim w2 As Worksheet
Dim w1 As Worksheet
Dim w3 As Worksheet
Dim SW As String
Dim qrycolvaly As Range
Dim qrycolvalt As Range
Dim r As Range
Dim canceled(1 To 5) As String
Dim performed(1 To 5) As String
Dim startcell As Range


    Set w2 = ThisWorkbook.Worksheets(2)
    Set w1 = ThisWorkbook.Worksheets(1)
    Set w3 = ThisWorkbook.Worksheets(3)
    Set startcell = w1.Range("B9")
    Set r = w3.Range("C1").End(xlDown)
    Set qrycolvaly = w3.Range("C1", r)

    For i = 1 To 200
        With w2
            SW = .Cells(i + 1, 3).Value
            If IsError(WorksheetFunction.Match(SW, qrycolvaly, 0)) = True Then
            performed(i).Value = SW
          End With
        w1.startcell.Offset(i - 1, 0).Value = performed(i).Value
    Next i

End Sub

When it arrives to the line performed(i).Value = SW I obtain the error:

Compiling error: invalid qualifier

clarification: my "performed" variable was dimmed as a string array to store values of worksheet(2) that doesn't have a match within the worksheet(3) range specified.

Could anyone explain me what am I doing wrong?

Mathieu Guindon · Accepted Answer · 2018-01-19 21:32:17Z

1

Dim performed(1 To 5) As String

So every index of the performed array is a String.

performed(i).Value = SW

Strings in VBA aren't objects, they don't have members - "SomeString".Value isn't legal.

Remove the .Value.

There's another problem though.

If IsError(WorksheetFunction.Match(SW, qrycolvaly, 0)) = True Then
    performed(i).Value = SW

That If block is missing an End If to be compilable. And then if IsError(SW) is True, you'll be assigning an Error value to a String, which will raise run-time error 13 / Type Mismatch.

answered Jan 19, 2018 at 21:32

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Compiling error :invalid qualifier when using an array to store not matching data within a loop

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related