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

Hi,

It is vlookup.... Use vlookup function.

=vlookup(A1, B:C, 2)

Shafiee.

"JayL" wrote in message
...
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

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" wrote in message
...
Hi,

It is vlookup.... Use vlookup function.

=vlookup(A1, B:C, 2)

Shafiee.

"JayL" wrote in message
...
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

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


"JayL" wrote:

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

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

Any ideas?


-----Original Message-----
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


"JayL" wrote:

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



.

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

--
Regards,
Tom Ogilvy



"JayLo" wrote in message
...
This produces the following error code...
Run Time error 91
Object variable or With Block variable not set

Any ideas?


-----Original Message-----
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


"JayL" wrote:

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



.

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

--
Regards,
Tom Ogilvy



"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy



"JayLo" wrote in message
...
This produces the following error code...
Run Time error 91
Object variable or With Block variable not set

Any ideas?


-----Original Message-----
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


"JayL" wrote:

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



.

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

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

--
Regards,
Tom Ogilvy



"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy



"JayLo" wrote in message
...
This produces the following error code...
Run Time error 91
Object variable or With Block variable not set

Any ideas?


-----Original Message-----
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


"JayL" wrote:

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



.

Tom - this is perfect.

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

"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy



"JayLo" wrote in message
...
This produces the following error code...
Run Time error 91
Object variable or With Block variable not set

Any ideas?


-----Original Message-----
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


"JayL" wrote:

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



.

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

--
Regards,
Tom Ogilvy



"JayL" wrote in message
...
Tom - this is perfect.

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

"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy



"JayLo" wrote in message
...
This produces the following error code...
Run Time error 91
Object variable or With Block variable not set

Any ideas?


-----Original Message-----
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


"JayL" wrote:

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



.

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


"JayL" wrote:

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