Searching one cell

[cbh35711]

So here's my search to a single cell
Dim sheetName As String
sheetName = ActiveSheet.Name
Dim find_criteria As Range
Set find_criteria = Worksheets(sheetName).Range(Selection).Find(What:="<", Lookat:=xlPart, LookIn:=xlFormula, MatchCase:=False)

I keep getting subscript out of range.

Any ideas?

Thanks,

Chris

[Kevin B]

If the code in your module is word wrapping the way the code in the post is,
you need to indicate that the code continues on the next line.

Set find_criteria = _
Worksheets(sheetName).Range(Selection).Find(What:="<", _
Lookat:=xlPart, LookIn:=xlFormula, MatchCase:=False)

The underscore, always preceded by a space, indicates that the code is
continued on the next line
--
Kevin Backmann


"cbh35711" wrote:

>
> So here's my search to a single cell
> Dim sheetName As String
> sheetName = ActiveSheet.Name
> Dim find_criteria As Range
> Set find_criteria =
> Worksheets(sheetName).Range(Selection).Find(What:="<",
> Lookat:=xlPart, LookIn:=xlFormula, MatchCase:=False)
>
> I keep getting subscript out of range.
>
> Any ideas?
>
> Thanks,
>
> Chris
>
>
> --
> cbh35711
> ------------------------------------------------------------------------
> cbh35711's Profile: http://www.excelforum.com/member.php...o&userid=30276
> View this thread: http://www.excelforum.com/showthread...hreadid=529619
>
>

[cbh35711]

That's just what this editor did to it...
I've got it as one line in my macro, but i keep getting subscript out of range error.

Thanks guys,

Chris

[Kanan]

your problem is xlformula. It should be xlformulas. Apparently somebody in
Microsoft doesn't know that formula is already plural.

good luck
kanan
"cbh35711" wrote:

>
> That's just what this editor did to it...
> I've got it as one line in my macro, but i keep getting subscript out
> of range error.
>
> Thanks guys,
>
> Chris
>
>
> --
> cbh35711
> ------------------------------------------------------------------------
> cbh35711's Profile: http://www.excelforum.com/member.php...o&userid=30276
> View this thread: http://www.excelforum.com/showthread...hreadid=529619
>
>

[cbh35711]

yeah...arg, ok that's an easy fix. I'll try it when i get to work tomorrow.

Thanks man last thing i have in my macro

Chris

[cbh35711]

Well that didn't work...
I added the s in xlFormulas, but i still get an error.
"Application defined or object defined error." sigh...

I'm just trying to see if there's a < in the current cell.
This is what i have now, just one line however this shows it.

Set find_criteria = Worksheets(sheetName).Range(Selection).Find(What:="<", Lookat:=xlPart, LookIn:=xlFormulas, MatchCase:=False)



Thanks for any help you can offer,

Chris

[Kanan]

I think there may be a problem with the 'Selection' clause. it worked When I
changed your code to this :
Set find_criteria = worksheets(sheetname).Range("A1:B500").Find(What:="<",
Lookat:=xlPart, LookIn:=xlFormulas, MatchCase:=False)

So you may want to change it to ' With .. End with' statement or specify
some kind of range like "A1:Z65000"... or something like that.

Kanan

"cbh35711" wrote:

>
> Well that didn't work...
> I added the s in xlFormulas, but i still get an error.
> "Application defined or object defined error." sigh...
>
> I'm just trying to see if there's a < in the current cell.
> This is what i have now, just one line however this shows it.
>
> Set find_criteria =
> Worksheets(sheetName).Range(Selection).Find(What:="<", Lookat:=xlPart,
> LookIn:=xlFormulas, MatchCase:=False)
>
>
>
> Thanks for any help you can offer,
>
> Chris
>
>
> --
> cbh35711
> ------------------------------------------------------------------------
> cbh35711's Profile: http://www.excelforum.com/member.php...o&userid=30276
> View this thread: http://www.excelforum.com/showthread...hreadid=529619
>
>

[cbh35711]

So is there a way that i can search only one cell?
I can't really put in a specific range in like that, because i want it to just be the active cell.

I've also tried
set rng = selection
and then trying range(rng), but that doesn't work either...

Any idea anyone?

Thanks

Chris

[Kanan]

this should work:

sheetname = ActiveSheet.Name
Worksheets(sheetname).Select
With ActiveCell
Set find_criteria = .Find(What:="<", Lookat:=xlPart, LookIn:=xlFormulas,
MatchCase:=False)
End With


"cbh35711" wrote:

>
> So is there a way that i can search only one cell?
> I can't really put in a specific range in like that, because i want it
> to just be the active cell.
>
> I've also tried
> set rng = selection
> and then trying range(rng), but that doesn't work either...
>
> Any idea anyone?
>
> Thanks
>
> Chris
>
>
> --
> cbh35711
> ------------------------------------------------------------------------
> cbh35711's Profile: http://www.excelforum.com/member.php...o&userid=30276
> View this thread: http://www.excelforum.com/showthread...hreadid=529619
>
>