match function code?

[JayL]

All,
Looking for code to read contents of cell A1, search column C for any cell
that contains A1 anywhere in a cell and return the row number of any finds
in Column C to cell B1.
I used the match function and it works but has a 255 character cell
limitation, which I need to surpass.

TIA,
J

 

[JayL]

Mohamed,
VLOOKUP requires a table and doesn't seem to work on the text fields I'm
working with. Unless my understanding is off.
-J

 

[Mohamed Shafiee]

Hi,

It is vlookup.... Use vlookup function.

=vlookup(A1, B:C, 2)

Shafiee.

 

[Guest]

this works for me, but have not tried it with 255+ chars

Sub FindA1()
Dim Rng As Range

Set Rng = Range("C:C").Find(What:=Range("A1"))
Range("B1") = Rng.Row

End Sub

 

[Guest]

Have not tried this with 255+ characters but otherwise it works for me:

Sub FindA1()
Dim Rng As Range

Set Rng = Range("C:C").Find(What:=Range("A1"))
Range("B1") = Rng.Row

End Sub

 

[JayLo]

This produces the following error code...
Run Time error 91
Object variable or With Block variable not set

Any ideas?

 

[Tom Ogilvy]

this worked fine with a result string longer than 255 characters.

Sub FindString()
Dim sStr As String, sStr1 As String
Dim rng As Range
Dim fAddr As String
sStr = Range("A1")
sStr1 = ""
Set rng = Columns(3).Find(What:=sStr, _
After:=Range("C65536"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
fAddr = rng.Address
Do
sStr1 = sStr1 & rng.Row & ", "
Set rng = Columns(3).FindNext(rng)
Loop While rng.Address <> fAddr
End If
sStr1 = Left(sStr1, Len(sStr1) - 1)
Range("B1").Value = sStr1

End Sub

 

[Tom Ogilvy]

It also works with strings in column C of greater than 255 characters. If
there is only cell that will contain the value, then you can simplify it to:


Sub FindString()
Dim sStr As String
Dim rng As Range
sStr = Range("A1")
Set rng = Columns(3).Find(What:=sStr, _
After:=Range("C65536"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
Range("B1").Value = rng.Row
End if
End Sub

If the strings are build with formulas, then you would change xlFormulas to
xlValues

 

[JayL]

Tom - this is perfect.

Is there a way to automatically go to A2 and perform the same routine, then
A3, etc?
TIA.
Jay

 

[Guest]

Tom,

I went back and tested both you reply as well as mine. the both worked for
me as long as A1 was less than 256 char but both failed beyond that:

Unable to get the Find property error

Any ideas?

 

[Tom Ogilvy]

Guess that is a third way to interpret the 255 problem.

For the OP's situation, the difference is that yours provides the 91 error
when the target isn't found and depends on the current setting of the find
command (which is probably why it wasn't found).

 

[Tom Ogilvy]

Sub FindString()
Dim cell as Range
Dim rng As Range
Dim rng3 as Range
set rng3 = range(Cells(1,1),Cells(rows.count,1).End(xlup))
for each cell in rng
set rng = nothing
Set rng = Columns(3).Find(What:=cell.value, _
After:=Range("C65536"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
cell.offset(0,1).Value = rng.Row
End if
Next
End Sub

 

[JayL]

Thanks Tom
I do get a error 424 Object required on
for each cell in rng
Any insight?
-J

 

[Tom Ogilvy]

my insight would be a typo

Sub FindString()
Dim cell as Range
Dim rng As Range
Dim rng3 as Range
set rng3 = range(Cells(1,1),Cells(rows.count,1).End(xlup))
for each cell in rng3 ' <== change to rng3
set rng = nothing
Set rng = Columns(3).Find(What:=cell.value, _
After:=Range("C65536"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
cell.offset(0,1).Value = rng.Row
End if
Next
End Sub