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]

Oops, my bad.

Makes things a lot easier, maybe.

Once you've found the parent you should know which row to start the search for the child in.

Also if you know the row of the parent you can determine the row of the next parent.

So now you should know the entire range to search, or at least the rows.

Parent row - start of search.

Next parent below - end of search.

You could even just do it from the parent row, and you might not even need to use find.

Anway, this uncommented, barely tested code seems to go some way to working with the data you posted.

Option Explicit
 
Sub test()
Dim rngLookIn As Range
Dim rngFound As Range
Dim strParent As String
Dim strChild As String
Dim NextParent As Long
    strParent = "hello"
    
    strChild = "three"
    
    Set rngLookIn = Range("A:A")
    
    Set rngFound = rngLookIn.Find(What:=strParent, After:=rngLookIn.Cells(1, 1), LookAt:=xlWhole)
    
    If rngFound Is Nothing Then
        MsgBox "Parent not found"
    Else
        Debug.Print "Parent found in row: " & rngFound.Row
        NextParent = rngFound.End(xlDown).Row - 1
        
        Set rngLookIn = Range("C" & rngFound.Row & ":C" & NextParent)
        
        Set rngFound = rngLookIn.Find(What:=strChild, After:=rngLookIn.Cells(1, 1), LookAt:=xlWhole)
        
        If rngFound Is Nothing Then
            MsgBox "Child not found"
        Else
            Debug.Print "Child found in row: " & rngFound.Row
        End If
    End If
    
End Sub

 

[vbaNewby]

Thanks Norie, I'll give this a try and let you know.

 

[vbaNewby]

Oops, my bad.

Makes things a lot easier, maybe.

Once you've found the parent you should know which row to start the search for the child in.

Also if you know the row of the parent you can determine the row of the next parent.

So now you should know the entire range to search, or at least the rows.

Parent row - start of search.

Next parent below - end of search.

You could even just do it from the parent row, and you might not even need to use find.

Anway, this uncommented, barely tested code seems to go some way to working with the data you posted.

Option Explicit
 
Sub test()
Dim rngLookIn As Range
Dim rngFound As Range
Dim strParent As String
Dim strChild As String
Dim NextParent As Long
    strParent = "hello"
    
    strChild = "three"
    
    Set rngLookIn = Range("A:A")
    
    Set rngFound = rngLookIn.Find(What:=strParent, After:=rngLookIn.Cells(1, 1), LookAt:=xlWhole)
    
    If rngFound Is Nothing Then
        MsgBox "Parent not found"
    Else
        Debug.Print "Parent found in row: " & rngFound.Row
        NextParent = rngFound.End(xlDown).Row - 1
        
        Set rngLookIn = Range("C" & rngFound.Row & ":C" & NextParent)
        
        Set rngFound = rngLookIn.Find(What:=strChild, After:=rngLookIn.Cells(1, 1), LookAt:=xlWhole)
        
        If rngFound Is Nothing Then
            MsgBox "Child not found"
        Else
            Debug.Print "Child found in row: " & rngFound.Row
        End If
    End If
    
End Sub

Hi Norie, after looking at this code, I cannot hardcode C into my range. The child node could reside anywhere, not just in column C.

My string can be something like this "one:two:three:four:five", so if one is found in column b, five would need to have a rngLookIn of column F.

 

[Norie]

Should be quite an easy fix actually, and I was wondering if something like that might be needed.

All you would need to do is get the no of elements, using UBound perhaps, in the array after the split.

That should give you the column, or at least something that you can use to get the column - perhaps an offset from the first column if that can also change.

Something like this, which returns column E.

Dim arr
Dim ChildCol As Long
 
    arr = Split("one:two:three:four:five", ":")
   
    ChildCol = UBound(arr) +1  ' add 1 because Split always returns 0-based arrays
    
    MsgBox "Last child column is " & Columns(UBound(arr) + 1).Address

With that we can set up the range to look for the child like this.

Set rngLookIn = Range(Cells(rngFound.Row, ChildCol), Cells(NextParent, ChildCol))

I think that should work anyway - don't have time to test it right now.

 

[vbaNewby]

The first rnglookin is not finding the parent. Here is my code.

If (subAcct = True) Then
                   
                    Set rngLookIn = Range("A:A")
                    MsgBox arrDown
                     Set foundsub = rngLookIn.Find(What:=arrDown, After:=rngLookIn.Cells(1, 1), LookAt:=xlWhole)
                        
                        If Not foundsub Is Nothing Then
                                MsgBox "finding from parent"
                          
                             nextParent = foundsub.End(xlDown).Row - 1
                             ChildCol = UBound(arr) + 1
                             MsgBox "Last child column is " & Columns(UBound(arr) + 1).Address
                             Set rngLookIn = Range(Cells(rngFound.Row, ChildCol), Cells(nextParent, ChildCol))
                             
                             Set FoundCell = rngLookIn.Find(What:=myCell, After:=rngLookIn.Cells(1, 1), LookAt:=xlWhole)
                        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

It is returning "Didn't find parent"

Does anyone else know why in the world my original cells.find does not work when plugging in the After:= argument?

I really think this should work as intended and not have to code around it.

 

[Norie]

What do you mean by 'code around' it?