for each in range gives variant array

[mcgurkle]

Hello,

I have a range, which i define as:

Set rngSearch = ws.UsedRange.Rows(intRow)


I then call the following function:

strTemp = FindMatchAddr(rngSearch, "Text")


where:

Function FindMatchAddr(rngSearch As Range, strFind As String) As
String
'returns the address of the cell in rngSearch that contains strFind

Dim cTemp As Range

For Each cTemp In rngSearch
If Trim(CStr(cTemp.Value)) = strFind Then
FindMatchAddr = cTemp.AddressLocal
Exit Function
End If
Next cTemp

FindMatchAdd = "not found"

End Function


Normally, I would expect this to go through and check each cell in
rngSearch. Instead, it is giving me a "type incompatibilty" error,
and the spy window tells me that cTemp.value is a "Variant/Variant(1
to 1, 1 to 66) ".

By being a bit more precise (using cTemp.Cells(1,1).Value), I can get
around this, but I've used For Each cTemp in rngRange structures
before and it has always worked.

I think that this problem stems from my having misunderstood something
simple, i'd very much appreciate it if someone could explain to me why
it is happening.

Thanks

 

[Guest]

Your problem is your are passing in an entire "ROW" instead of 256 cell.
Change your set statement to

Set rngSearch = ws.UsedRange.Rows(intRow).Cells

and it should work OK.

 

[mcgurkle]

Your problem is your are passing in an entire "ROW" instead of 256 cell.
Change your set statement to

Set rngSearch = ws.UsedRange.Rows(intRow).Cells

and it should work OK.

--
regards,
Tom Ogilvy

















- Show quoted text -

That makes sense. Many thanks for your help, it's made things clearer
and fixed my code.