Geek Answers Handbook

There must be a variant or object for each control variable.

Really new to VBA here ... I looked around and tried to compile some kind of code to suit my needs. Think about it almost there, but I get errors that are probably easy to overcome, and yet I don't know how to do this.

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

Hope this makes sense. I copied a code that might 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

I am currently receiving the error message "There must be a variant or object for each control variable," but cannot find anywhere, which explains why this is so. I am sure this is pretty obvious, but the manual will be really appreciated.

Thanks.

+4
source share
1 answer

You cannot use the String variable in the For Everyone line. You use tartget and cand as control variables in For Each loops, but you defined them as strings. They must be an object, and specifically an object that contains a collection of objects that you iterate. You are repeating a range, which is a set of ranges, so your control variables must 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 did not fix the error:

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

You don't need to define .Cells at the end of the For Each line, but I like it. You can .Rows over .Rows or .Columns or .Areas using a range (although .Cells is the default value).

There is nothing wrong with StrConvert, but you can also use LCase () or, like me, StrComp.

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

+8
source

More articles:


All Articles