if cell equals x then clear contents from another cell macro

[mjd]

Hi,

I am trying to code out the following scenario for a macro:

If cell A# contains "total reconconciled portfolios" then clear contents of columns B# & I#

Essentially I am trying to clear the N/A's from columns B & I in the specific rows where column A includes that phrase. I can't clear all the errors on the sheet, because there will be legitimate N/A's that we will have to research in the data.

Is this possible? Sorry if this is an easy question, I have figured out what I know about VBA by deconstructing macros, so there is a lot of basic stuff that I missed along the way.

Here is the code I've already written.

Sub accts()
    Sheets("APIAccounts").Select
    Rows("1:7").Select
    Selection.delete Shift:=xlUp
    Columns("B:B").Select
    Selection.delete Shift:=xlToLeft
    Columns("C:S").Select
    Selection.delete Shift:=xlToLeft
    Columns("B:J").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(5, 1)), TrailingMinusNumbers:=True
    Columns("B:B").Select
    Selection.delete Shift:=xlToLeft
    Columns("B:I").Select
    Selection.delete Shift:=xlToLeft
    Cells.Select
 
    Sheets("New Recon Sheet").Select
    Application.ScreenUpdating = False
        Application.Calculation = xlCalculationManual
        Dim i, LastRow
        LastRow = Range("A" & Rows.Count).End(xlUp).Row
        For i = LastRow To 1 Step -1
        If Cells(i, "A").Value = "" Then
        Cells(i, "A").EntireRow.delete
        End If
        Next
    Application.Calculation = xlCalculationAutomatic
 
    Sheets("Statements").Select
    Range("D1").Select
    ActiveCell.Formula = "=LEFT(A1,3)&""-""&RIGHT(A1,6)"
    Range("D1").Select
    Selection.AutoFill Destination:=Range("D1:D300")
    Range("D1:D300").Select
    Sheets("New Recon Sheet").Select
    Range("I9").Select
    ActiveCell.Formula = _
        "=if(a9="""","""",IF(VLOOKUP(B9,Statements!D:D,1,FALSE)=B9,""Y"",""N""))"
    Range("I9").Select
    Selection.AutoFill Destination:=Range("I9:I300")
    Range("I9:I221").Select
 
    Sheets("New Recon Sheet").Select
    Range("B9").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Cut
    Range("M9").Select
    ActiveSheet.Paste
    Range("B9").Select
    ActiveCell.Formula = _
        "=IF(A9="""","""",IF(AND(A9<>"""",M9<>""""),M9,VLOOKUP(A9,APIAccounts!A:B,2,FALSE)))"
    Range("B9").Select
    Selection.AutoFill Destination:=Range("B9:B300")
    Range("B9:B300").Select
    Range("A4").Select
    Selection.NumberFormat = "m/d/yyyy"
 
End Sub

 

[mikerickson]

You could use this

Dim oneCell As Range

With ThisWorkbook.Sheets("Sheet1").Range("A:A"): Rem adjust
    For Each oneCell in Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
        With oneCell
            If LCase(CStr(.Value)) Like "*total reconconciled portfolios*" Then
                .EntireRow.Range("B1").ClearContents
                .EntireRow.Range("I1").ClearContents
            End If
        End With
    Next oneCell
End With

 

[mjd]

Thanks Mike,

That worked perfectly!

Thanks again!
Mike