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

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

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

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



.

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



.

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

--
Regards,
Tom Ogilvy

"gocush" /delete wrote in message
...
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



.

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



.

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


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



.

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

--
Regards,
Tom Ogilvy


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


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



.