With Range?

[Jenawade]

Can someone please help me fine tune this macro? In item #7, as shown below (which is not always in the same place, it may be shifted up or down a fews rows and/or a column in the various workbooks) I'm looing for an instance of 130620, 130618, 133362, 130604. If that exists, then any instance of either Blue or Orange next to "finished kits to be placed" below, needs to be replaced with Black (this is also not always in the same place). The macro I have now will do the work, but, it looks for ANY instance of those 4 part numbers and changes ANY instance of Blue or Orange to Black. In a test the end user performed, he had a file with "blue" in the description which was changed to black.

Sub test()

    Dim strPath As String
    Dim strFile As String
    Dim wkb As Workbook
    Dim wks As Worksheet
    
    strPath = "H:\Quality\Shared\Chrysler p.s. from Garrett for test"  'Change the path to your folder
    
    If Right(strPath, 1) <> "\" Then strPath = strPath & "\"
    
    strFile = Dir(strPath & "*.xls")
    
    Do While Len(strFile) > 0
        Set wkb = Workbooks.Open(strPath & strFile)
        For Each wks In wkb.Worksheets
     
               On Error Resume Next
               
               Range("A46:Z90").Select
               
               r = Cells.find(130620, LookIn:=xlValues)
               s = Cells.find(130618, LookIn:=xlValues)
               t = Cells.find(133362, LookIn:=xlValues)
               u = Cells.find(130604, LookIn:=xlValues)

               If Not r = "" Then Cells.Replace What:="BLUE", Replacement:="BLACK", LookAt:=xlPart
               If Not s = "" Then Cells.Replace What:="BLUE", Replacement:="BLACK", LookAt:=xlPart
               If Not t = "" Then Cells.Replace What:="BLUE", Replacement:="BLACK", LookAt:=xlPart
               If Not u = "" Then Cells.Replace What:="BLUE", Replacement:="BLACK", LookAt:=xlPart
               If Not r = "" Then Cells.Replace What:="ORANGE", Replacement:="BLACK", LookAt:=xlPart
               If Not s = "" Then Cells.Replace What:="ORANGE", Replacement:="BLACK", LookAt:=xlPart
               If Not t = "" Then Cells.Replace What:="ORANGE", Replacement:="BLACK", LookAt:=xlPart
               If Not u = "" Then Cells.Replace What:="ORANGE", Replacement:="BLACK", LookAt:=xlPart
          
        Next wks
        wkb.Close savechanges:=True
        strFile = Dir
    Loop
    
    MsgBox "Completed", vbInformation
    
End Sub

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
45	UNIT LABEL REQUIREMENTS
46	1. *THE UNIT LABEL SHOULD HAVE CUSTOMER NUMBER:								52128420AA
47	2. *THE BARCODE SHOULD READ:					52128420AA					*	*	*
48	3. *THE CUSTOMER CODE IS:					55087AG					*	*	*
49	4. * THE COUNTRY OF ORIGIN SHOULD BE:							MEXICO
50	5. *THE QUANTITY SHOULD BE BEFORE THE CUSTOMER NUMBER.											*	*
51	6. *THE DATE MUST APPEAR ON THE LABEL.							*	*	*	*	*	*
52	7. *THE PART NUMBER OF THE LABEL IS:						133362 l-23						*
53	8. THE MEASUREMENT IS:					2X3
136102

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
63	MISC. INFORMATION
64	FINISHED KITS TO BE PLACED ON							BLACK		SKID
65	NO CORRUGATED SHIPPER NEEDED
66	NO OVERPACK LABELS NEEDED
136102

 

[p45cal]

you only want to search in Range("A46:Z90")?
At the moment, your searching and your replacing takes place over the whole sheet because you've used Cells as the qualifier, instead use the range you want to restrict searching/replacing to:

with Range("A46:Z90")[INDENT] r = [B][COLOR=Red].[/COLOR][/B]find(130620, LookIn:=xlValues)
s = [B][COLOR=Red].[/COLOR][/B]find(130618, LookIn:=xlValues)
'...
If Not r = "" Then [B][COLOR=Red].[/COLOR][/B]Replace What:="BLU etc.
[/INDENT]end with

 

[Jenawade]

I added in the range at the last minute to see if that would do it, but it didn't work. I only know enough about VBA to get myself into trouble often!

 

[Jenawade]

Oh, I get it, add the 'with' and periods to only work that area. Thanks so much!