cell.address / variable with cell.find

[vbaNewby]

Hi guru's!

I have an array where I get the first element of the array and I want to use the that string to first lookup that string and then use that to find my next string but start the find after the address of the first found string:

 If InStr(myCell, ":") Then
            MyArray = Split(myCell, ":")
            arr = MyArray(UBound(MyArray))
            myCell = arr
            subAcct = True
            arrDown = MyArray(LBound(MyArray))
         End If

Set FoundSub = Cells.Find(what:=arrDown, After:=ActiveCell, LookIn:=xlFormulas _
                , LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=True)
                
                Set FoundCell = Cells.Find(what:=myCell,[U][B] After:=FoundSub[/B][/U], LookIn:=xlFormulas _
                , LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=True)

The problem is with the After:= argument. I've tried FoundSub.address, that does not work either.

So I'm trying to tell the macro to start my find after FoundSub.address, how can I accomplish this???

Thanks in advance.

 

[Norie]

Have you tried just FoundSub?

 

[vbaNewby]

Have you tried just FoundSub?

Yes, I have, it compiles fine but errors when it runs.

 

[Norie]

What error do you get?

The After argument should be a range object like FoundSub - it's not just the address.

Try checking to see if anything has actually been found.

This should go after the first find.

If FoundSub Is Nothing Then
       MsgBox "Search term not found, continuing with next search term."
 
Else
      ' do next part of search
       Set FoundCell = Cells.Find(what:=myCell,After:=FoundSub, LookIn:=xlFormulas _
                , LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=True)
End If

By the way, do you really want to search every cell on the workhsheet?

Can you not narrow down the range to search to something smaller than Cells.

 

[vbaNewby]

Hi Norie,

Thanks a bunch for the reply. Here is my code:

 If (subAcct = True) Then
                    Set FoundSub = Cells.Find(what:=arrDown, After:=ActiveCell, LookIn:=xlFormulas _
                    , LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=False, SearchFormat:=True)
                    MsgBox FoundSub.Address
                        If Not FoundSub Is Nothing Then
                            Set FoundCell = Cells.Find(what:=myCell, After:=FoundSub, LookIn:=xlFormulas _
                            , LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                            MatchCase:=False, SearchFormat:=True)
                        Else
                            MsgBox "Didn't find parent"
                            Set FoundCell = Cells.Find(what:=myCell, After:=ActiveCell, LookIn:=xlFormulas _
                            , LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                            MatchCase:=False, SearchFormat:=True)
                        End If
                End If

What seems to be happening is that the FoundSub variable is set and working correctly.

When trying to set FoundCell to something, it is still picking up my first occurence of "myCell" in the worksheet instead of the second occurence (which is "after" my FoundSub find.

So it looks like its not using FoundSub address to start the search....???

 

[Norie]

I think if you narrowed down the search it might help.

I've had similar problems and it was doing that, or similar, which resolved the issue.

If I recall correctly what I did was to specifically alter the range to look in based on the first find.

eg if searching A1:A100 and found something in A20, next search would be in A21:A100 and so on

 

[vbaNewby]

I think if you narrowed down the search it might help.

I've had similar problems and it was doing that, or similar, which resolved the issue.

If I recall correctly what I did was to specifically alter the range to look in based on the first find.

eg if searching A1:A100 and found something in A20, next search would be in A21:A100 and so on

I don't understand how this is not working in the first place. Unless the "after" argument in cells.find is not working as intended?

How can I narrow the search down to specifically alter the range?

 

[Norie]

Not sure if as intended is the right way to put it, perhaps more like not as expected.

Where do you want to search?

At the moment by using Cells you are searching every cell on the worksheet, that might actually be affecting the after part too.

Is there a specific range/column/row you want to search?

 

[vbaNewby]

Not sure if as intended is the right way to put it, perhaps more like not as expected.

Where do you want to search?

At the moment by using Cells you are searching every cell on the worksheet, that might actually be affecting the after part too.

Is there a specific range/column/row you want to search?

Well, I kind of need to search every cell for my keyword.

What I am trying to do is pretty simple:

1 - Find a keyword for a subaccount. Works just fine.
2 - Find another keyword that comes AFTER my subaccount keyword.

After I find 2, then I use a range to copy the data I want... Seems like this would be easy...

Thanks again.

 

[Norie]

You need to search every single cell on the worksheet, all 10 billion* of them.

Are the subaccounts not in a particular column or group of columns?

You could even narrow the search down to the used range.
*That might be slightly out.