VBA Code to Clear Cell Content After Appearance of String

[pynergee]

Good morning everyone.

I have several workbooks, similar to below, and I am hoping to delete some costing information out of the worksheets. As shown below, the information in Red is what I hope to clear. However, this information does not always originate in the same cell (otherwise I could just do a Range.clear). I am looking for a way to find "Unit Cost" (shown in Blue) in the worksheet, and select the cell ([X-3][Y+1]), where cell XY is the location of "Unit Cost". (The X-3, Y+1 is standard among all worksheets). Then I would like to select a range +6 columns and +15 rows, and clear such, to ensure all the costing information has been removed.

The main problem I am having is locating the "Unit Cost" cell and then moving the selection to the appropriate starting position.

Thank you,
Marc


Please see example below:

Sheet 1

[TABLE="width: 864"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 2"]Company Information[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cost Analysis[/TD]
[TD][/TD]
[TD]# of Contacts[/TD]
[TD]25[/TD]
[TD][/TD]
[TD][/TD]
[TD]Lot Size [/TD]
[TD]500[/TD]
[TD="align: right"]25.16[/TD]
[TD="colspan: 2"] Assembly Rate [/TD]
[/TR]
[TR]
[TD="colspan: 2"]Part # XXXXXXXXXXX[/TD]
[TD]C-Clip[/TD]
[TD="align: right"]28[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]April 17, 2013[/TD]
[TD][/TD]
[TD]Hand Load[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Set Up[/TD]
[TD]Run [/TD]
[TD]Burdened[/TD]
[TD] Cost per [/TD]
[TD] Total [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]Set Up/Hrs[/TD]
[TD]Run rate[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Hours[/TD]
[TD]Hrs[/TD]
[TD]Rate[/TD]
[TD] Piece [/TD]
[TD] Cost [/TD]
[/TR]
[TR]
[TD]Molding[/TD]
[TD][/TD]
[TD]0[/TD]
[TD="align: right"]0[/TD]
[TD]per hour[/TD]
[TD][/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD] - [/TD]
[TD] - [/TD]
[/TR]
[TR]
[TD]Deflashing[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]180[/TD]
[TD]per hour[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2.78[/TD]
[TD="align: right"]25.16[/TD]
[TD] 0.140[/TD]
[TD] 69.89[/TD]
[/TR]
[TR]
[TD]Load Contacts[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]36[/TD]
[TD]per hour[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]13.89[/TD]
[TD="align: right"]25.16[/TD]
[TD] 0.699[/TD]
[TD] 349.44[/TD]
[/TR]
[TR]
[TD]C-Clip[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]67[/TD]
[TD]per hour[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7.46[/TD]
[TD="align: right"]25.16[/TD]
[TD] 0.376[/TD]
[TD] 187.76[/TD]
[/TR]
[TR]
[TD]Stamping[/TD]
[TD][/TD]
[TD]0.25[/TD]
[TD="align: right"]480[/TD]
[TD]per hour[/TD]
[TD][/TD]
[TD="align: right"]0.25[/TD]
[TD="align: right"]1.04[/TD]
[TD="align: right"]25.16[/TD]
[TD] 0.065[/TD]
[TD] 32.50[/TD]
[/TR]
[TR]
[TD]Inspection[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]400[/TD]
[TD]per hr[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1.25[/TD]
[TD="align: right"]25.16[/TD]
[TD] 0.063[/TD]
[TD] 31.45[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Handling/Packagin/Shipping/Misc[/TD]
[TD][/TD]
[TD="align: right"]800[/TD]
[TD]per hr[/TD]
[TD][/TD]
[TD] [/TD]
[TD="align: right"]0.63[/TD]
[TD="align: right"]25.16[/TD]
[TD] 0.031[/TD]
[TD] 15.73[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Total[/TD]
[TD]XXX[/TD]
[TD]XXX[/TD]
[TD]XXX[/TD]
[TD] XXX [/TD]
[TD] XXX [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Comment[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Mat'l Cost[/TD]
[TD]XXX[/TD]
[TD] XXX [/TD]
[TD] XXX [/TD]
[/TR]
[TR]
[TD="colspan: 2"]1. ASSEMBLY INFORMATION 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]2. ASSEMBLY INFORMATION 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD]Total Cost[/TD]
[TD]XXX[/TD]
[TD] XXX [/TD]
[TD] XXX [/TD]
[/TR]
[TR]
[TD="colspan: 2"]STAMP "XXXXXXXX"[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3. RECODED BY XXX[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Last Sold:[/TD]
[TD] XXX [/TD]
[TD] XXX [/TD]
[/TR]
[TR]
[TD]4. RECODED XXX[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]PREFERRED WAY: AUTOMATIC C-CLIPPER[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Actual Margin[/TD]
[TD]XXX[/TD]
[TD]XXX[/TD]
[/TR]
[TR]
[TD="colspan: 3"]600 SERIES INSPECTION PRIOR TO CONTACT LOADING[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]REVIEWED XXX[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Breakeven Selling Price[/TD]
[TD] XXX [/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Current[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Type[/TD]
[TD]No.[/TD]
[TD]Description[/TD]
[TD] [/TD]
[TD]Qty[/TD]
[TD]U/M[/TD]
[TD]Cost[/TD]
[TD] [/TD]
[TD][/TD]
[TD]Unit Cost[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Item[/TD]
[TD]03214-001[/TD]
[TD="colspan: 2"](E) C3214-1 MLDG, 600-1-25SD11[/TD]
[TD="align: right"]1[/TD]
[TD]EA[/TD]
[TD="align: right"]0.5498[/TD]
[TD="align: right"]0.5498[/TD]
[TD][/TD]
[TD] 0.54982[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Item[/TD]
[TD]04232-001P-50[/TD]
[TD="colspan: 2"]04232-001P CONT,SOC, 50AU/5AU/[/TD]
[TD="align: right"]25[/TD]
[TD]EA[/TD]
[TD="align: right"]0.0648[/TD]
[TD="align: right"]1.6188[/TD]
[TD][/TD]
[TD] 0.06475[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Item[/TD]
[TD]03203-001-50[/TD]
[TD="colspan: 2"]03203-001 SOC GDE - 50AU/100CU[/TD]
[TD="align: right"]2[/TD]
[TD]EA[/TD]
[TD="align: right"]0.1874[/TD]
[TD="align: right"]0.3748[/TD]
[TD][/TD]
[TD] 0.18738[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Item[/TD]
[TD]04203-000-FL[/TD]
[TD="colspan: 2"]04203-000 C-CLIP - AUFL/(50-10[/TD]
[TD="align: right"]25[/TD]
[TD]EA[/TD]
[TD="align: right"]0.0032[/TD]
[TD="align: right"]0.0810[/TD]
[TD][/TD]
[TD] 0.00324[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Item[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.0000[/TD]
[TD][/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Item[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.0000[/TD]
[TD][/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Item[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.0000[/TD]
[TD][/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Item[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.0000[/TD]
[TD][/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Item[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.0000[/TD]
[TD][/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Item[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.0000[/TD]
[TD][/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Item[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.0000[/TD]
[TD][/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Item[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.0000[/TD]
[TD][/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Item[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.0000[/TD]
[TD][/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2.6243[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Comfy]

Cells.Find(What:="Unit Cost", After:=[A1], LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate

so something like this:

Sub Macro1()


Dim uCost As Range


Set uCost = Cells.Find(What:="Unit Cost", After:=[A1], LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
        
        Range(Cells(uCost.Row + 1, uCost.Column - 3), Cells(uCost.Row + 16, uCost.Column)).ClearContents
End Sub

 

[pynergee]

Comfy, this worked as intended. I am trying to modify it to apply to all the worksheets in a workbook, and all the workbooks in a folder:

Option Explicit

Sub Macro1()


    Dim sh As Worksheet
    Const fPath As String = "C:\Users\m.raby\Desktop\Continental Acquisition\Cost Analysis\"
    Dim sName As String
    Dim uCost As Range
    With Application
        .Calculation = xlCalculationManual
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    
    sName = Dir(fPath & "*.xls*")
    
    Do Until sName = ""
    
    For Each sh In ActiveWorkbook.Worksheets


        Set uCost = Cells.Find(What:="Unit Cost", After:=[A1], LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)


[B]        Range(Cells(uCost.Row + 1, uCost.Column - 3), Cells(uCost.Row + 16, uCost.Column)).ClearContents[/B]
        On Error Resume Next
        sh.Range("A1") = sh.Name
    
    Next sh
    Loop
    
    With Application
        .Calculation = xlAutomatic
        .EnableEvents = True
        .ScreenUpdating = True
    End With
    
End Sub

When I try to run this, I am getting the error: uCostColumn= < Object Variable or With block variable not set>. Also, is there a way to ignore a worksheet if the cell "Unit Cost" does not exist, since I am trying to perform this in a loop?

 

[pynergee]

Note:

I got the code to run for all worksheets that have "Unit Cost" in it, but it breaks when it does not encounter a cell that contains "Unit Cost", and it seems that's why I was getting the error previously.

Can I ignore a worksheet and move on to the next if it does not encounter that specific "Unit Cost"?

Sub ClearCosting()Dim sh As Worksheet
Dim uCost As Range
For Each sh In ActiveWorkbook.Sheets
    sh.Activate
    Set uCost = Cells.Find(What:="Unit Cost", After:=[A1], LookIn:=xlFormulas, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)


    Range(Cells(uCost.Row + 1, uCost.Column - 3), Cells(uCost.Row + 16, uCost.Column)).ClearContents
Next sh
End Sub