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.

 

[vbaNewby]

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.

I need to search every single cell after my sub account.

NO, the subaccounts are not in a particular column, would be great if they were but they are not.

"You could even narrow the search down to the used range."

This might be an option. I can start the search after the subaccount address. I've tried this already but still not working. Any other suggestions to accomplish this?

  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
                                MsgBox "finding from parent"
                            Set FoundCell = Cells.Find(what:=myCell, After:=Range(FoundSub.Address).Select, LookIn:=xlFormulas _
                            , LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlDown, _
                            MatchCase:=False, SearchFormat:=True)
                            MsgBox FoundCell.Address
                        Else
                            MsgBox "Didn't find parent"
                            Set FoundCell = Cells.Find(what:=myCell, After:=ActiveCell, LookIn:=xlFormulas _
                            , LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlDown, _
                            MatchCase:=False, SearchFormat:=True)
                        End If
                End If

Errors out! Even tried selecting my range before the second search...

 

[Norie]

Sorry but I really can't see why you need to search every single cell on the worksheet.

Unless you have data in every single cell that is.

By the way how exactly did you try my first suggestion of using FoundSub?

Perhaps if you tell us more about your data it might clear a few things up.

 

[vbaNewby]

Sorry but I really can't see why you need to search every single cell on the worksheet.

Unless you have data in every single cell that is.

By the way how exactly did you try my first suggestion of using FoundSub?

Perhaps if you tell us more about your data it might clear a few things up.

I need to search every cell after my subAccount. There could be data in any cell after that point.

I tried using After:=FoundSub, which doesn't error but doesn't give me the desired results.

More about data: I have a long string stored in a cell, for example

Hello:World:Test

I break these words up and store them in an array. Then my intention is to lookup "Test" after the word "Hello"

I need to do this because it is a possibility that the word "Test" is before my word "Hello"

I need to pickup the correct "Test"

Using the code I've pasted above is actually giving me the wrong "Test", it is giving back the first instance of "Test" and not the one I want (after "Hello")

.... thanks again.

 

[Norie]

Is it not possible to expand a little on the data structure?

I was perhaps being a bit flippant earlier but there are that many cells, probably a lot more, on a worksheet - well in Excel 2010 anyway.

If you use Cells.Find you are going to search every single cell on the worksheet.

You could narrow it down to UsedRange.Find but that might not work especially well if your data is all over the place.

 

[vbaNewby]

Is it not possible to expand a little on the data structure?

I was perhaps being a bit flippant earlier but there are that many cells, probably a lot more, on a worksheet - well in Excel 2010 anyway.

If you use Cells.Find you are going to search every single cell on the worksheet.

You could narrow it down to UsedRange.Find but that might not work especially well if your data is all over the place.

If I set the range to be huge, then it might work. I'll give it a try.

 

[vbaNewby]

If I set the range to be huge, then it might work. I'll give it a try.

Example of UsedRange.find ???

I'm not sure why this wouldn't work, if I select the range and use ActiveCell, it should start the search after that address. Why is it not?

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
                    Range(foundsub.Address).Select
                        If Not foundsub Is Nothing Then
                                MsgBox "finding from parent"
                            Set FoundCell = UsedRange.Find(what:=myCell, After:=ActiveCell, LookIn:=xlFormulas _
                            , LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlDown, _
                            MatchCase:=False, SearchFormat:=True)
                            MsgBox FoundCell.Address
                        Else
                            MsgBox "Didn't find parent"
                            Set FoundCell = Cells.Find(what:=myCell, After:=ActiveCell, LookIn:=xlFormulas _
                            , LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlDown, _
                            MatchCase:=False, SearchFormat:=True)
                        End If
                End If

 

[Norie]

Sorry but I don't think you quite understand.

You don't want to set the range to search as huge, it should be as small as it can be.

Is there absolutely no way you know which column(s) any of the values you are looking for could be in?

 

[vbaNewby]

Sorry but I don't think you quite understand.

You don't want to set the range to search as huge, it should be as small as it can be.

Is there absolutely no way you know which column(s) any of the values you are looking for could be in?

I understand. The data will be reside in the first 10 columns 99.9% of the time but there is that chance that it could be outside of that range but very rare.

Can I get this accomplished with a column range?

Still would like to know why my original find after does not work. Thanks for the time Norie.

 

[Norie]

It's probably because you are using Cells that it doesn't work.

I did sort of mention that being a problem in post #8 or thereabouts.

You should only search the cells that will have data in them.

If that changes then it's not hard to deal with - the range you search doesn't need to be hardcoded.

Without more information all I can suggest is you use UsedRange or something similar.

If you do use it use it in place of Cells everywhere.

Without more information it's really hard to help further.

This is about the best I can come up with, and it's rubbish - I know it's not going to work.

Dim ws As Worksheet
Dim rngLookIn As Range
Dim rngAfter As Range
Dim FoundSub As Range
Dim FoundCell As Range
Dim LookFor
 
    Set ws = Worksheets("Sheet1")    ' change name of worksheet to suit
   
    If InStr(myCell, ":") Then
        MyArray = Split(myCell, ":")
        arr = MyArray(UBound(MyArray))
        myCell = arr
        subAcct = True
        LookFor = MyArray(LBound(MyArray))
    End If

    Set rngLookIn = ws.UsedRange    ' set the range to look in to the used range
    Set rngAfter = rngLookIn.Cells(1, 1)    ' set the range to look after as the top left cell in the range being looked in

    Set FoundSub = rngLookIn.Find(what:=arrDown, After:=rngAfter, LookIn:=xlFormulas _
                                                                          , LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                                  MatchCase:=False, SearchFormat:=True)
 
    If FoundCell Is Nothing Then
        MsgBox "Not found"
    Else
        Set rngAfter = FoundSub    ' change cell to look after
 
        Set FoundCell = rngLookIn.Find(what:=myCell, After:=rngAfter, LookIn:=xlFormulas _
                                                                              , LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                                       MatchCase:=False, SearchFormat:=True)
    End If

 

[vbaNewby]

What else do you need to know about my data set? I basically have a bunch of different cells that contains data like so:

hello:worldne
hello:world:two
hello:world:three
test:again:test2
test:again:test3

and they are display in another worksheet like so (each in their own column)

hello
->world
->->one
->->two
->->three

test
->again
->->test2
->->test3