[dba-Tech] Excel Find problem

Arthur Fuller fuller.artful at gmail.com
Thu Nov 1 08:51:56 CDT 2007


I'm having trouble with Excel's find method. I know that the target data is
there, but the Find method keeps returning Nothing and I can't discern my
error. Here is the code:

<code>
                Dim intr As Integer
                Dim MatchedCell As Range
                With CntrySheet.Range("B1:B50")
                     Set MatchedCell = Cells.Find(What:="Asset Mix of
Underlying Funds:", _
                                                After:=ActiveCell, _
                                                LookIn:=xlValues, _
                                                LookAt:=xlWhole, _
                                                SearchOrder:=xlByRows, _
                                                SearchDirection:=xlNext, _
                                                MatchCase:=False, _
                                                SearchFormat:=False)

                    If Not MatchedCell Is Nothing Then
                        intr = MatchedCell.Row + 2
                    Else
' a band-aid until I figure out what's wrong with the search
                        intr = 18
                    End If
                End With
</code>


I read somewhere that Excel remembers your last Find settings, which is why
I specify them all, but it still doesn't work. I can see the target string,
it's right there on row 18, but I cannot guarantee that it will always be on
row 18. I have also substituted "walk the column" code for the above,
comparing each entry to the target string -- which works, but I would like
to know why my Find code is failing.

TIA,
Arthur



More information about the dba-Tech mailing list