for each control variable must be variant or object

Question

Really new to VBA here... I've looked around and tried to piece together some code to fulfil my need. Think it's almost there, but I'm getting errors that are likely easy to overcome and yet I don't know how.

The code looks at the current sheet (STOCK), and takes a 'target' text value from cell A2. It then searches a named range in another sheet 'Other'. If it determines one of the cells ('cand') in Other to be equal to the target value, then a value of "True" will be applied to column G in the STOCK sheet, on the same row of the original target.

Hopefully this makes sense. I've copied in the code which will maybe shed more light on things.

Dim target As String
Dim cand As String
Dim currentrow As Integer

Sub search_named_range()

    ' This range is hard coded; we can try A:A if hard code version works '
    For Each target In Worksheets("STOCK").Range("A2:A1000")

    ' retrieve the row of the current range, for use when setting target values '
    currentrow = Range(target).Row

        ' FOR loop to search range of part numbers in Mojave '
        For Each cand In Worksheets("Other").Range("N9:N150")
            If StrConv(cand.Value, 2) = StrConv(target, 2) Then
                Worksheets("STOCK").Range("G" + currentrow) = "True"
                GoTo FORend
            End If
        Next cand

' If part is not found, do nothing and return to find next target '
FORend: Next target

End Sub

Currently I'm getting the error 'For Each control variable must be Variant or Object', but can't find anywhere that explains why this is. I'm sure it's pretty obvious, but a steer would be really appreciated.

Thanks.

Solution

You can't use a String variable in a For Each. You're using tartget and cand as the control variables in your For Each loops but you have defined them as strings. They need to be an object, and specifically an object that is contained the collection of objects you're iterating. You're iterating over a range, which is a collection of ranges, so your control variables need to be Range objects.

Sub search_named_range()

    Dim rCell As Range
    Dim rCand As Range

    For Each rCell In Worksheets("STOCK").Range("A2:A1000").Cells
        For Each rCand In Worksheets("Other").Range("N9:N150").Cells
            If StrComp(rCand.Value, rCell.Value, vbTextCompare) = 0 Then
                rCell.Offset(0, 6).Value = "True"
                Exit For 'exits the rCand For, but no the rCell one
            End If
        Next rCand
    Next rCell

End Sub

Other changes that weren't correcting errors:

I'm not sure why you declared your variables outside the sub, but I put them inside.

You don't need to define .Cells at the end of the For Each line, but I like to. You could iterate over .Rows or .Columns or .Areas with a Range (although .Cells is the default).

There's nothing wrong with StrConvert, but you could also use LCase() or, as I do, StrComp.

Since I already have a reference to a cell on the current row (rCell), I use that and Offset to fill in a column I want.

Comments

[Admin]

This is absolutely perfect, thanks so much for your help! I can see now that several of the things I was trying were a bit roundabout (e.g. getting the row number), and I didn't realise you couldn't use Strings in For Each - really appreciate your explanation and suggestions for alternative methods, the code now works just as I had originally intended. Cheers!