Thread: Find and Replace with multiple conditions problem.

Hey all,
Hopefully someone can help me with this problem I'm having, as I can't seem to find a solution anywhere online or in the help files that works for me. I'm currently trying to write some code that will search a row for a specific string, and once found, will then look at the cell to the immediate left for a different specific string. If it finds both, it will then copy the contents of the cell 2 rows to the left into a different cell on a seperate sheet. This is what I've come up with so far:

Code:

Sub Find_MH()
    Dim FindString As String
    Dim Rng As Range
    FindString = "MH"
    If Trim(FindString) <> "" Then
        With Sheets("Data").Range("D:D")
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not Rng Is Nothing And Rng.Offset(0, -1).Value = "Available" Then
                Sheet1.Range("B29").Value = Rng.Offset(0, -2).Value
                Rng.Offset(0, -1).Value = "Allocated"
                Else
                MsgBox "No MH Found"
            End If
        End With
    End If
End Sub

That code will search down the D column for the string "MH" and then check for the string "Available" in the adjacent cell. What I can't figure out is how to properly use the FindNext command to keep searching if it doesn't find the "Available" string in the adjacent cell, until both conditions are met, and then execute the other code.

Hopefully I've explained it so that other people can understand what I'm trying to do.
Any help would be greatly appreciated

Chris

Try this:

Code:

Option Explicit

Sub Find_MH()
Dim FindString As String
Dim strFind As Range, strFirst As Range
    
    FindString = "MH"
    If Trim(FindString) <> "" Then
        With Sheets("Data").Range("D:D")
            Set strFind = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
                If Not strFind Is Nothing Then
                    Do
                        If strFirst Is Nothing Then Set strFirst = strFind
                        If strFind.Offset(0, -1).Value = "Available" Then
                            Sheet1.Range("B29").Value = strFind.Offset(0, -2).Value
                            strFind.Offset(0, -1).Value = "Allocated"
                            Exit Do
                        Else
                            Set strFind = .FindNext(After:=strFind)
                        End If
                    Loop Until strFind.Address = strFirst.Address
                Else
                    MsgBox "No MH Found"
                End If
        End With
    End If
End Sub

Post up a workbook, make sure it's clear what you want as "results".

This is set to find and transfer one match. You want it to run and find them all at once?

My bad, that was a rookie error. Sorry about that. I edited the listing in post #2 so that it reads as complete and correct.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

I thought that was in there. Please post an updated example book w/macro so I can see that...

There's no need for a backup-qa macro, the one macro is supposed to keep looping for you until one is found. If your address the cells directly instead of trying to offset, then you can target the "allocated" message to column C no matter where the QA is found, you know?

example:

Code:

removed...see below

I've changed all the macros accordingly to demonstrate and simplify.

On the sheet I uploaded? Please provide more specifics, when an error occurs, DEBUG and report the line of code that stalled, also hover your mouse over all the variables on and near that row to note the current values of those variables. Thanks.

Meanwhile, I got tunnelvision testing the "no QA" message, so I got that working and forgot to verify the form would find names normally, which it stopped doing. This fix should do that, forgot some periods...pesky little things.

Example:

Code:

Sub Find_QA()
Dim FindString As String
Dim strFind As Range, strFirst As Range
    
    FindString = "QA"
    If Trim(FindString) <> "" Then
        With Sheets("Data").Cells
            Set strFind = .Find(What:=FindString, _
                    After:=.Cells(.Cells.Count), _
                    LookIn:=xlValues, LookAt:=xlWhole, _
                    SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=False)
            If Not strFind Is Nothing Then
                Do
                    If strFirst Is Nothing Then Set strFirst = strFind
                    If .Cells(strFind.Row, "C").Value = "Available" Then
                        Sheet1.Range("B28").Value = .Cells(strFind.Row, "B").Value
                        .Cells(strFind.Row, "C") = "Allocated"
                        Exit Sub
                    Else
                        Set strFind = .FindNext(After:=strFind)
                        If strFind.Address = strFirst.Address Then
                            MsgBox "No QA Found"
                            Exit Sub
                        End If
                    End If
                Loop Until strFind.Address = strFirst.Address
            Else
                MsgBox "No QA Found"
                Exit Sub
            End If
        End With
    End If
End Sub