Simple find?

[wolf.stalker]

ok, about ready to put a rock through my PC :banghead:

I have a worksheet. On this workshet (in column A) contains store numbers. now, I do a ctrl+f and type in a store number (while recording a macro) and it finds the first occurance of it. THIS IS GREAT AND ALL I FRIGGIN WANT!

now, i take that macro that just recorded and walk through it WITHOUT changing a thing and get a friggin Run-time error 1004.

all night it has been like this.

anyone have any ideas? oh here's the code.

What do i need to add so that it selects the cell of the first instance like the functions performs when run through Excel (not a macro?)



Public Sub FindData()

Dim iCount As Integer ' counts the # of times it shows up
Dim iSiteCount As Integer ' counts the number of stores on sheet

iSiteCount = Application.WorksheetFunction.CountA(Sheets(4).Range("B:B")) ' this part works fine and returns a value of 1702

iCount = Application.CountIf(Sheets("Tank Size Info").Range("A:A"), Sheets("Form").Range("A2")) ' this part works find and retruns a value of 3

Sheets("Tank Size Info").Select ' select my target sheet to look at

MsgBox Sheets("Form").Range("A2") ' to see if what i am looking for is on the sheet...as it happens to be 68809, i took the long code out and just used the number until i can figure out the issue. Pops up 68809

MsgBox Cells.Find(What:=Sheets("Form").Range("A2"), LookAt:=xlWhole)
'Sheets("Form").Range("A2") ' also pops up 68809

Cells.Find(What:="68809", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
True, SearchFormat:=False).Activate 'crashes everytime

End Sub


ok, what am i doing wrong here? what am i missing or forgetting?

[Bob Phillips]

This works for me



Public Sub FindData()

Dim iCount As Integer ' counts the # of times it shows up
Dim iSiteCount As Integer ' counts the number of stores on sheet

iSiteCount = Application.WorksheetFunction.CountA(Sheets(4).Range("B:B"))

iCount = Application.CountIf(Sheets("Tank Size Info").Range("A:A"), Sheets("Form").Range("A2"))

With Sheets("Tank Size Info")

.Select

MsgBox Sheets("Form").Range("A2")

.Cells.Find(What:=68809, After:=.Range("A1"), LookIn:=xlFormulas, LookAt:=xlWhole).Activate
End With
End Sub

[p45cal]

I'm guessing that the (exact) value might not be found. Try:Set Foundcell = Cells.Find(What:=Sheets("Form").Range("A2"), After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, SearchFormat:=False)
If Foundcell Is Nothing Then
MsgBox "Nothing found"
Else
Foundcell.Select
MsgBox Foundcell.Value
End If
Note the .Activate has been removed.

[wolf.stalker]

first off, is this site laggy to anyone but me? it has taken me close to 30 min to get here to post a reply!

and this is now this like my 6th attempt to post a reply!! What a week!

XLD -- thanks for adding tags, i never have figured out how to do that :-\

p45cal -- although your code may work, it does not fix my issue.

if you look at my code again, you will notice that i tell you the value via msgbox's and comments too. i know the value i am looking for is in the sheet simply because as i stated, i had JUST COPPIED from the macro creator.
to be honest, this is like the 4th or 5th thing I have done since having to move up to 2007 that i KNOW should work, but errors out.
new approach. lets ASSUME the code i listed works (and for some reason works for you guys). what is different from 2003 to 2007 (what i now have) that might cause my problems? is there an option or add-in or something that i might be missing or not have selected?

[wolf.stalker]

first off, is this site laggy to anyone but me? it has taken me close to 30 min to get here to post a reply! and this is now my (i have lost count of how many times i ahve tried to post this) attempt to post a reply!!

XLD -- thanks for adding tags, i never have figured out how to do that :-\

p45cal -- although your code may work, it does not fix my issue.

if you look at my code again, you will notice that i tell you the value via msgbox's and comments too. i know the value i am looking for is in the sheet simply because as i stated, i had JUST COPPIED from the macro creator.

to be honest, this is like the 4th or 5th thing I have done since having to move up to 2007 that i KNOW should work, but errors out.


new approach. lets ASSUME the code i listed works (and for some reason works for you guys). what is different from 2003 to 2007 (what i now have) that might cause my problems? is there an option or add-in or something that i might be missing or not have selected?

[wolf.stalker]

ah, my confusion builds.

so while still trying to figure out WTF is going on here, i took my sheet that has data and pasted it in a new workbook. i then took the "find" routine above and put it into a module for that new workbook.

yeah, worked like it's suppose to. :think: now i wonder why it works in the new book and not the old.

could it be some type of crazy format on the orgianl data sheet that is preventing the find from working? come to think of it, cells.select also errors out on that sheet too. hmm :think:

[wolf.stalker]

ok, i got it to work.

had to select all the cell first cells.select (which mind u i have tried this too, but always crashed here) then do the find.

the reason it was not working is because i kept trying to use cells.select instead of Worksheets(4).Cells.Select :motz2: and that little bit there was worth ~ 4 hrs of frustration but i am happy to report i am back on track and ready for my next "problem".

[Bob Phillips]

the reason it was not working is because i kept trying to use cells.select instead of Worksheets(4).Cells.Select :motz2: and that little bit there was worth ~ 4 hrs of frustration but i am happy to report i am back on track and ready for my next "problem".

If you notice, I wrapped it in a With statement, which effectively identifies the worksheet as well as the range.

[Bob Phillips]

[XLD -- thanks for adding tags, i never have figured out how to do that :-\

It is very simple, select the part of the message that is code and click the green VBA button at the top of the message pane.

Or ... the way I do it is to type open square bracket and VBA and close square bracket before the code and open square bracket and \VBA and close square bracket after the code.