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

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

<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[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