Reputation: 73
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.
Upvotes: 7
Views: 23375
Reputation: 33185
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.
Upvotes: 14