for each in range gives variant array
 

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")


whe

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

for each in range gives variant array
 

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




"mcgurkle" wrote:

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")


whe

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

for each in range gives variant array
 

On 7 Nov, 16:02, Tom Ogilvy
wrote:
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



"mcgurkle" wrote:
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")

whe

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- Hide quoted text -

- Show quoted text -

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