vba conditional find & replace

Question

Today I am stuck with this code while trying to replace a dot (.) but only if in the list from sheet1 does not match one of the values from another list of another sheet (same workbook). "Argument NOT optional" is the error but it would not give me any other hint.

Sub Filter(wsS As Worksheet, wsN As Worksheet, i As Integer, j As Integer, k As Integer, l As Integer, a As Integer) ' ' substitute Macro ' Application.ScreenUpdating = False Range("a1").FormulaR1C1 = "Sorted" Set wsS = Sheets("sheet1") Set wsN = Sheets("non_confid") col1 = "A" col2 = "E" col3 = "C"

For a = 1 To 200
     If wsS.Range(col1 & a) = wsN.Range("AB2:ab600") Then
        a = a + 1
        Else: Range(col1 & a).Replace What:=".", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        a = a + 1
     End If
Next

Range("a1").AutoFilter
ActiveWorkbook.Worksheets("sheet1").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("sheet1").AutoFilter.Sort.SortFields.Add Key:=Range("A:A"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("sheet1").AutoFilter.Sort
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

Range("a1").Select
With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With
    With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .Color = 15773696
    .TintAndShade = 0
    .PatternTintAndShade = 0
End With

With Selection.Font
    .Color = -10477568
    .TintAndShade = 0
End With

ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$a$500"), , xlYes).Name = "Table1"
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:="<>"

    Set wsS = Sheets("sheet1") 'this has assigned the worksheet sheet1 to wsS
    Set wsN = Sheets("non_confid") 'this has assigned the worksheet sheet1 to wsU

    i = 2

    For j = 2 To 300
        If Not IsEmpty(wsS.Range(col1 & j).Value) Then
            wsS.Range(col3 & i - 1).Value = wsS.Range(col1 & j).Value
            i = i + 1
        End If
    Next

Range("A:B").EntireColumn.Delete

    For k = 1 To 300
           If IsEmpty(wsS.Range(col1 & k).Value) Then
                i = i + 1 'Exit For 'this jumps out of the loop, so no more copying.
           Else
                wsN.Range(col2 & i).Value = wsS.Range(col1 & k).Value
                i = i + 1
           End If
    Next

Sheets("non_confid").Select
Columns("A:G").EntireColumn.AutoFit
Range("e1").Select
ActiveSheet.ListObjects("Status").Range.AutoFilter Field:=4, Criteria1:="<>"
Range("A1").Select
ActiveWorkbook.Saved = True
Application.ScreenUpdating = True

End Sub

Thank you in advance for your time!

I also have some more columns that compare the final list with more lists to see if the result matches with any of their values. The trick is that since I have two sets of values, I do not wish to use 2 workbooks and the dot makes that difference — Laurentiu Mirica

Andy G Andy G · Accepted Answer · 2013-07-25T11:36:41

The only required arguments for Replace are What and Replacement, which you have supplied.

I do not receive an error message about this but I do with this line:

If wsS.Range(col1 & a) = wsN.Range("AB2:ab600") Then

because you are trying to compare a single value with a Range.

(I'm assuming that wsS and wsN are set correctly to worksheets.)

This is the likely cause of any errrors. But as your error message is different I am suspicious that you are not posting your actual code, or enough of it for us to replicate your problem.

vba conditional find & replace

1 Answers