MS Excel VBA Code - Replace the value or statement in a row, if the value is anywhere in the row. The Values Stored as a Name Range in another Sheet

[OilEconomist]

Thanks in advance for your assistance.

I am getting the error "Run-time error '438': Object does not support this property or method"

I did see the following post which I was trying to use for assistance.

VBA Bulk Find and Replace or Similar

(1) Versus using just finding one value with the variant “Fnd”, how can a find a series of values in Sheet1 and replace them with “”? Basically, just removing the values in the row, but the row keeps the other values, but just without the value set to “Fnd”. The series of values will be stored in another sheet “Sheet2” in the range Rng2 (“A2:A” & LasRow. LastRow) will be found each time I run the macro. This will allow me to add and remove values in the list.

I would like to name range them (RemoveValues) and use that if possible. I was trying to use an array, but not sure how to set it up. Search different sites, but could not figure it out.

(2) How would I do the same thing with a partial find, but if it does a partial find, delete the row?
(3) How would I do the same thing, but if I find the exact values, delete the row?

I’ve seen this done with loops, storing the values manually in an array, etc., but I’m trying to complete the find based on values from another sheet where I can add and remove the values (i.e. change).

Sub FndRplc_w_Array()
    
    '____________________________________________________________________________________________________
    'Dimensioning
        Dim LastRow1 As Long
        Dim LastRow2 As Long

        Dim ActvSheetName As String

        Dim ActvSheet As Worksheet

        Dim Fnd As Variant
        Dim Rplc As Variant

        Dim Rng1 As Range
        Dim Rng2 As Range


    '____________________________________________________________________________________________________
    'Define remove values range and set range
        
        Sheets("Sheet2").Activate
        LastRow2 = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
        
        Set Rng2 = Range("B2:B" & LastRow2)
               
        ThisWorkbook.Names.Add Name:="RemoveValues", RefersTo:=Rng2
        
        
    '____________________________________________________________________________________________________
    'Activate the sheet
        Sheets("Sheet1").Activate
    
    
    '____________________________________________________________________________________________________
    'Define find and replace criteria
        Sheets("Sheet1").Activate
        LastRow1 = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
        
        Fnd = Range("RemoveValues").Values
        Rplc = ""


    '____________________________________________________________________________________________________
    'Set the range and then perform find and replace
        
        Set Rng1 = ActiveSheet.Range("A8:A" & LastRow)

        For i = 1 To UBound(Fnd, 1)
            Rng1.Replace What:=Fnd, Replacement:=Rplc, LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False
        Next
    
    '____________________________________________________________________________________________________
    'End sub
    


End Sub

 

[GWteB]

The "member not found" Run-time error is most likely thrown on execution of this line

Fnd = Range("RemoveValues").Values

Removing the trailing "s" of the (invalid) Values property will fix this error.
Would suggest to enable the "Auto List Members" option within the VBE, so its intellisense is able to help you. In addition to that, also the use of the Option Explicit statement at the top of each code module is recommended (take a look over here).

Your Fnd variable is most likely to carry an array type. You've acknowledged that within this line,

For i = 1 To UBound(Fnd, 1)

but you did not in the next line, so ammend it like:

Rng1.Replace What:=Fnd(i, 1), Replacement:=Rplc, LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False

Hopefully this will set you back on track.

 

[OilEconomist]

The "member not found" Run-time error is most likely thrown on execution of this line

Fnd = Range("RemoveValues").Values

Removing the trailing "s" of the (invalid) Values property will fix this error.
Would suggest to enable the "Auto List Members" option within the VBE, so its intellisense is able to help you. In addition to that, also the use of the Option Explicit statement at the top of each code module is recommended (take a look over here).

Your Fnd variable is most likely to carry an array type. You've acknowledged that within this line,

For i = 1 To UBound(Fnd, 1)

but you did not in the next line, so ammend it like:

Rng1.Replace What:=Fnd(i, 1), Replacement:=Rplc, LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False

Hopefully this will set you back on track.

Thanks so much for your response. I amended the code., but I still think I have issues.

So "Sheet2" has values I want to check in Column D of "Sheet1." So what I am doing is defining the range of values with "RemoveValues" which are contained in "Sheet2" then go to column D of "Sheet2" and replace those with find and replace.

The issue is that when I do the

For i =1 to UBound(Fnd, 1)

within Sheet1, isn't that just using the length of LastRow2 which is based on Sheet2 where Sheet1 has a different number of row (LastRow1).

I can't figure out how to fix it.

Sub FndRplc_w_Array()
    
    '____________________________________________________________________________________________________
    'Dimensioning
        Dim LastRow1 As Long
        Dim LastRow2 As Long

        Dim ActvSheetName As String

        Dim ActvSheet As Worksheet

        Dim Fnd As Variant
        Dim Rplc As Variant

        Dim Rng1 As Range
        Dim Rng2 As Range


    '____________________________________________________________________________________________________
    'Define remove values range and set range
        
        Sheets("Sheet2").Activate
        LastRow2 = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
        
        Set Rng2 = Range("B2:B" & LastRow2)
               
        ThisWorkbook.Names.Add Name:="RemoveValues", RefersTo:=Rng2
        
        
    '____________________________________________________________________________________________________
    'Activate the sheet
        Sheets("Sheet1").Activate
    
    
    '____________________________________________________________________________________________________
    'Define find and replace criteria
        Sheets("Sheet1").Activate
        LastRow1 = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
        
        Fnd = Range("RemoveValues").Value
        Rplc = ""


    '____________________________________________________________________________________________________
    'Set the range and then perform find and replace
        
        Set Rng1 = ActiveSheet.Range("A8:A" & LastRow)

        For i = 1 To UBound(Fnd, 1)
            Rng1.Replace What:=Fnd, Replacement:=Rplc, LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False
        Next
    
    '____________________________________________________________________________________________________
    'End sub

 

[GWteB]

You're welcome!

I amended the code., but I still think I have issues.

You just partially did that and yes, the code still has issues. I have now looked at it a little more thoroughly and will briefly summarize it.

• in general, activating a worksheet through VBA code is not necessary; just set a proper reference or use the With ... End With qualifier construct and go from there.
• you've got three Long type variables: LastRow1, LastRow2 and LastRow. The first two are both explicitly declared and both are assigned values to. The latter is implicitly declared (by usage) but no value is assigned to it, so will always contain 0 and therefore will throw you a run-time error. If you had actually used Option Explicit like I suggested in my previous post, the compiler would have warned you about this coding error.
• you've got two Range type variables and both names contain (like the Lastrow variables) a number. I would advise you to make a clearer distinction in the naming: which one is used as the source range and which one as the target range.
• you're about to search for multiple values, but when found each value should be replaced by one and the same value; you better declare the variable used as a replacement As String rather than As Variant.

In cases where a Worksheet object or Range object is used once (like your case), I prefer the With ... End With construct, rather than declaring a proper object type variable and Set-ing it.
My code would look like below, where I left yours as comments for better comparison.

Option Explicit

Public Sub OilEconomist()
    
' So "Sheet2" has values I want to check in Column D of "Sheet1."
' So what I am doing is defining the range of values with "RemoveValues" which are contained
' in "Sheet2" then go to column D of "Sheet2" and replace those with find and replace.

' The issue is that when I do the VBA Code:
' For i =1 to UBound(Fnd, 1)
' within Sheet1, isn't that just using the length of LastRow2 which is based on Sheet2
' where Sheet1 has a different number of row (LastRow1).
' I can't figure out how to fix it.
    
'''    '____________________________________________________________________________________________________
'''    'Dimensioning
'''    Dim LastRow1 As Long, LastRow2 As Long
'''    Dim Rng1 As Range, Rng2 As Range
    
    Dim Fnd     As Variant
    Dim Rplc    As String
    Dim i       As Long

'''    '____________________________________________________________________________________________________
'''    'Define remove values range and set range
'''    Sheets("Sheet2").Activate
'''    LastRow2 = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row   ' <<<  this produces the last row of column A ...
'''    Set Rng2 = Range("B2:B" & LastRow2)                              ' <<<  ... and is used as row boundary in column B
'''    ThisWorkbook.Names.Add Name:="RemoveValues", RefersTo:=Rng2      ' <<<  adding & using a named range are two redundant code steps, so I left them out
    
    ' >>> fill variable "Fnd" with values to search for, which therefore becomes an Array type variable <<<<
    With ThisWorkbook.Worksheets("Sheet2")
        Fnd = .Range("B2:B" & .Cells(.Rows.Count, "B").End(xlUp).Row).Value
    End With
        
'''    '____________________________________________________________________________________________________
'''    'Activate the sheet
'''    Sheets("Sheet1").Activate

'''    '____________________________________________________________________________________________________
'''    'Define find and replace criteria
'''    Sheets("Sheet1").Activate
'''    LastRow1 = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
'''    Fnd = Range("RemoveValues").Value

    Rplc = ""

'''    '____________________________________________________________________________________________________
'''    'Set the range and then perform find and replace
'''    Set Rng1 = ActiveSheet.Range("A8:A" & LastRow)

    With ThisWorkbook.Worksheets("Sheet1")
        With .Range("A8:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)
            For i = 1 To UBound(Fnd, 1)
                .Replace What:=Fnd(i, 1), Replacement:=Rplc, LookAt:=xlPart, _
                         SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                         ReplaceFormat:=False
            Next
        End With
    End With
End Sub

 

[OilEconomist]

@GWteB Thanks so much and that works. Apologies for not using Option Explicit and the With ... End With qualifier construct. Not use to using them and now I know why coders use the With so often. I didn't like it because of all the extra lines, but now, I know why it's used. I marked it answer my post, but there were two more questions. Do you think I should start a new post for them, or can they be answered here?

They were as follows:
2) How would I do the same thing with a partial find, but if it does a partial find, delete the row?
(3) How would I do the same thing, but if I find the exact values, delete the row?

 

[GWteB]

No need for apologies, it doesn't hurt anyone the way you code. I just gave a few tips that could benefit your current query (and could prevent some future ones) and it's your choice whether to redeem the advantage of it.

2) How would I do the same thing with a partial find, but if it does a partial find, delete the row?

Then we can't use the Replace method anymore. Instead we use the Find method and the FindNext method. If we encounter a cell which contains what we're looking for, then we mark that cell for deletion. If the search is completed we delete the rows on which these cells are in one go.

(3) How would I do the same thing, but if I find the exact values, delete the row?

Replace the LookAt parameter of the Find method (currently xlPart) with xlWhole.

Public Sub OilEconomist_r2()
    
    Dim Fnd             As Variant
    Dim i               As Long
    Dim c               As Range
    Dim RowsToDelete    As Range
    Dim FirstAddress    As String

    With ThisWorkbook.Worksheets("Sheet2")
        Fnd = .Range("B2:B" & .Cells(.Rows.Count, "B").End(xlUp).Row).Value
    End With

    With ThisWorkbook.Worksheets("Sheet1")
        With .Range("A8:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)
            For i = 1 To UBound(Fnd, 1)
                If Not IsEmpty(Fnd(i, 1)) Then
                    Set c = .Find(Fnd(i, 1), LookIn:=xlValues, LookAt:=xlPart)
                    If Not c Is Nothing Then
                        FirstAddress = c.Address
                        Do
                            If RowsToDelete Is Nothing Then
                                Set RowsToDelete = c
                            Else
                                Set RowsToDelete = Application.Union(RowsToDelete, c)
                            End If
                            Set c = .FindNext(c)
                        Loop While Not c Is Nothing And c.Address <> FirstAddress
                    End If
                End If
            Next i
            If Not RowsToDelete Is Nothing Then RowsToDelete.EntireRow.Delete
        End With
    End With
End Sub

 

[OilEconomist]

No need for apologies, it doesn't hurt anyone the way you code. I just gave a few tips that could benefit your current query (and could prevent some future ones) and it's your choice whether to redeem the advantage of it.

Then we can't use the Replace method anymore. Instead we use the Find method and the FindNext method. If we encounter a cell which contains what we're looking for, then we mark that cell for deletion. If the search is completed we delete the rows on which these cells are in one go.


Replace the LookAt parameter of the Find method (currently xlPart) with xlWhole.

Public Sub OilEconomist_r2()
   
    Dim Fnd             As Variant
    Dim i               As Long
    Dim c               As Range
    Dim RowsToDelete    As Range
    Dim FirstAddress    As String

    With ThisWorkbook.Worksheets("Sheet2")
        Fnd = .Range("B2:B" & .Cells(.Rows.Count, "B").End(xlUp).Row).Value
    End With

    With ThisWorkbook.Worksheets("Sheet1")
        With .Range("A8:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)
            For i = 1 To UBound(Fnd, 1)
                If Not IsEmpty(Fnd(i, 1)) Then
                    Set c = .Find(Fnd(i, 1), LookIn:=xlValues, LookAt:=xlPart)
                    If Not c Is Nothing Then
                        FirstAddress = c.Address
                        Do
                            If RowsToDelete Is Nothing Then
                                Set RowsToDelete = c
                            Else
                                Set RowsToDelete = Application.Union(RowsToDelete, c)
                            End If
                            Set c = .FindNext(c)
                        Loop While Not c Is Nothing And c.Address <> FirstAddress
                    End If
                End If
            Next i
            If Not RowsToDelete Is Nothing Then RowsToDelete.EntireRow.Delete
        End With
    End With
End Sub

@GWteB This works perfect. It will take me some time to fully understand the code. I appreciate you putting together these codes. I now understand why everyone uses the <With> and <End With>. So what I am going to do since you have provided a solution for my questions in more than one post is respond back so they both end up in one post and I can mark that as the solution.

 

[OilEconomist]

Thanks so much to @GWteB for the solutions which I have combined here.

This is to check a list of values in"Sheet2" Column B starting in B2 through the last entry in column B, against a list of values in "Sheet1" column A starting row 8 all the way through last entry in Column A.
(1) if anything from Sheet2 Column B is found in Sheet 1 Column A, it will replace it in Sheet1 with ""
(2) if anything from Sheet2 Column B is found in Sheet 1 Column A, it will delete the rows in Sheet1 based on a partial match
(3) if anything from Sheet2 Column B is found in Sheet 1 Column A for an exact match, it will delete the rows in Sheet1 based on an exact match.


(1) if anything from Sheet2 Column B is found in Sheet 1 Column A, it will replace the values in Sheet1 with ""

Option Explicit

Public Find_Replace()
   
'(1) if anything from Sheet2 Column B is found in Sheet 1 Column A, it will replace it in Sheet1 with ""
  
    Dim Fnd     As Variant
    Dim Rplc    As String
    Dim i       As Long


    With ThisWorkbook.Worksheets("Sheet2")
        Fnd = .Range("B2:B" & .Cells(.Rows.Count, "B").End(xlUp).Row).Value
    End With
    
    Rplc = ""


    With ThisWorkbook.Worksheets("Sheet1")
        With .Range("A8:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)
            For i = 1 To UBound(Fnd, 1)
                .Replace What:=Fnd(i, 1), Replacement:=Rplc, LookAt:=xlPart, _
                         SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                         ReplaceFormat:=False
            Next
        End With
    End With

End Sub

(2) if anything from Sheet2 Column B is found in Sheet 1 Column A, it will delete the rows in Sheet1 based on a partial match

Option Explicit
Public Sub Fnd_Delete ()

'(2) if anything from Sheet2 Column B is found in Sheet 1 Column A, it will delete the rows in Sheet 1 based on a partial match    

    Dim Fnd             As Variant
    Dim i               As Long
    Dim c               As Range
    Dim RowsToDelete    As Range
    Dim FirstAddress    As String

    With ThisWorkbook.Worksheets("Sheet2")
        Fnd = .Range("B2:B" & .Cells(.Rows.Count, "B").End(xlUp).Row).Value
    End With

    With ThisWorkbook.Worksheets("Sheet1")
        With .Range("A8:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)
            For i = 1 To UBound(Fnd, 1)
                If Not IsEmpty(Fnd(i, 1)) Then
                    Set c = .Find(Fnd(i, 1), LookIn:=xlValues, LookAt:=xlPart)
                    If Not c Is Nothing Then
                        FirstAddress = c.Address
                        Do
                            If RowsToDelete Is Nothing Then
                                Set RowsToDelete = c
                            Else
                                Set RowsToDelete = Application.Union(RowsToDelete, c)
                            End If
                            Set c = .FindNext(c)
                        Loop While Not c Is Nothing And c.Address <> FirstAddress
                    End If
                End If
            Next i
            If Not RowsToDelete Is Nothing Then RowsToDelete.EntireRow.Delete
        End With
    End With
End Sub

(3) if anything from Sheet2 Column B is found in Sheet 1 Column A for an exact match, it will delete the rows in Sheet1 based on exact match.
In solution (2), above this one, to get the aforementioned to work, just

change the following code:

Set c = .Find(Fnd(i, 1), LookIn:=xlValues, LookAt:=xlPart)

to the following:

Set c = .Find(Fnd(i, 1), LookIn:=xlValues, LookAt:=xlWhole)

 

[GWteB]

You're welcome and thanks for letting me know.