OilEconomist
Active Member
- Joined
- Dec 26, 2016
- Messages
- 439
- Office Version
- 2019
- Platform
- Windows
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).
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).
VBA Code:
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