Replace a word with another word when I print two different copies

[ShotwellKid]

Hello,

I have a very strange problem. I work in the QA department at my job and I am trying to develop a spreadsheet that has all kinds of awesome features. The problem I am running into is that I need to print an Internal copy for our records that shows if stuff failed or failed/passed the inspection but show pass on the Customer's copy but the fields that say N/A can say N/A on both. I will paste the code in this post. I am sure it is something very simple that I am missing. I have tried the substitute function and the replace function but whenever it gets to it I get a function not defined error. Any Suggestions?

*You will see where I have tried many different things with nesting substitute and all that down towards the problem. This is where the error occurs*

Private Sub CommandButton1_Click()

Dim historyQA As Worksheet
Dim inputQA As Worksheet
Dim sh As Worksheet

Dim nextRow As Long
Dim oCol As Long

Dim myRng As Range
Dim myCopy As String
Dim myCell As Range


Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="C:\Users\Apex\Desktop\QA_QC
CENTRAL FINAL.xls"
Application.DisplayAlerts = True

'cells to copy from Oven Quality Assurance sheet
myCopy = "B5,B7,B3,B4,B6,I3,G11,I11,B12,G15,I15,B16,G19,I19,B20,G23,I23,B24,G27,I27,B28,G31,I31,B32,G35,I35,B36"

Set inputQA = Worksheets("OVEN CHECK OUT")
Set historyQA = Worksheets("ALL DATA")

With historyQA
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With

With inputQA
Set myRng = .Range(myCopy)

' If Application.CountA(myRng) <> myRng.Cells.Count Then
' MsgBox "Please fill in all the cells!"
' Exit Sub
' End If
End With

With historyQA
oCol = 1
For Each myCell In myRng.Cells
historyQA.Cells(nextRow, oCol).Value = myCell.Value
oCol = oCol + 1
Next myCell
End With

'clear input cells that contain constants
'With inputQA
' On Error Resume Next
' With .Range(myCopy).Cells.SpecialCells(xlCellTypeConstants)
' .ClearContents
' Application.Goto .Cells(1) ', Scroll:=True
' End With
' On Error GoTo 0
'End With

'print CONSOLE CHECK OUT COMPLETE FOR INHOUSE RECORDS
Set sh = ActiveSheet
Worksheets("OVEN CHECK OUT").PageSetup.CenterHeader = "- INTERNAL USE ONLY -"
Worksheets(Array("OVEN CHECK OUT")).PrintOut
sh.Select

' clear out reinspected by for customer printout
Worksheets("OVEN CHECK OUT").Range("I11:I11").ClearContents
Worksheets("OVEN CHECK OUT").Range("I15:I15").ClearContents
Worksheets("OVEN CHECK OUT").Range("I19:I19").ClearContents
Worksheets("OVEN CHECK OUT").Range("I23:I23").ClearContents
Worksheets("OVEN CHECK OUT").Range("I27:I27").ClearContents
Worksheets("OVEN CHECK OUT").Range("I31:I31").ClearContents
Worksheets("OVEN CHECK OUT").Range("I35:I35").ClearContents

' clear out comments for customer printout
Worksheets("OVEN CHECK OUT").Range("B12:B12").ClearContents
Worksheets("OVEN CHECK OUT").Range("B16:B16").ClearContents
Worksheets("OVEN CHECK OUT").Range("B20:B20").ClearContents
Worksheets("OVEN CHECK OUT").Range("B24:B24").ClearContents
Worksheets("OVEN CHECK OUT").Range("B28:B28").ClearContents
Worksheets("OVEN CHECK OUT").Range("B32:B32").ClearContents
Worksheets("OVEN CHECK OUT").Range("B36:B36").ClearContents

' set all pass for customer printout
Worksheets("OVEN CHECK OUT").Cells(11, 7) = SUBSTITUTE(G11, "FAIL", "PASS") '11 equals column, 7 equals row
Worksheets("OVEN CHECK OUT").Cells(15, 7) = SUBSTITUTE(SUBSTITUTE(G15, "FAIL", "PASS"), "FAIL/PASS", "PASS")
Worksheets("OVEN CHECK OUT").Cells(19, 7) = SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(G19, "FAIL", "PASS", "PASS"), "N/A", "N/A"), "FAIL/PASS", "PASS")
Worksheets("OVEN CHECK OUT").Cells(23, 7) = SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(G23, "FAIL", "PASS", "PASS"), "N/A", "N/A"), "FAIL/PASS", "PASS")
Worksheets("OVEN CHECK OUT").Cells(27, 7) = SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(G27, "FAIL", "PASS", "PASS"), "N/A", "N/A"), "FAIL/PASS", "PASS")
Worksheets("OVEN CHECK OUT").Cells(31, 7) = SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(G31, "FAIL", "PASS", "PASS"), "N/A", "N/A"), "FAIL/PASS", "PASS")
Worksheets("OVEN CHECK OUT").Cells(35, 7) = SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(G35, "FAIL", "PASS", "PASS"), "N/A", "N/A"), "FAIL/PASS", "PASS")

'print OVEN CHECK OUT COMPLETE FOR CUSTOMER
Set sh = ActiveSheet
Worksheets("OVEN CHECK OUT").PageSetup.CenterHeader = "- CUSTOMER COPY -"
Worksheets(Array("OVEN CHECK OUT")).PrintOut
sh.Select

'clear input cells that contain constants
With inputQA
On Error Resume Next
With .Range(myCopy).Cells.SpecialCells(xlCellTypeConstants)
.ClearContents
Application.GoTo .Cells(1) ', Scroll:=True
End With
On Error GoTo 0

'set hidden form name
Worksheets("OVEN CHECK OUT").PageSetup.CenterHeader = " "
Worksheets("OVEN CHECK OUT").Cells(3, 9) = "OVEN"
Worksheets("OVEN CHECK OUT").Range("B3:B7").ClearContents
Worksheets("OVEN CHECK OUT").Range("G11:G11").ClearContents
Worksheets("OVEN CHECK OUT").Range("G15:G15").ClearContents
Worksheets("OVEN CHECK OUT").Range("G19:G19").ClearContents
Worksheets("OVEN CHECK OUT").Range("G23:G23").ClearContents
Worksheets("OVEN CHECK OUT").Range("G27:G27").ClearContents
Worksheets("OVEN CHECK OUT").Range("G31:G31").ClearContents
Worksheets("OVEN CHECK OUT").Range("G35:G35").ClearContents
End With

End Sub

[ShotwellKid]

Someone else initially created this spreadsheet so I am just trying to tweek it to fit new needs. I am also a newbie at this so please excuse me if this task either A) cant be done or B) I am going about it in the wrong way.

[rscsmith]

Hi

I'm not sure whether I want to help or not! Sounds dodgy, replacing FAIL with PASS on the Client's copy! I hope I'm not one of your customers! :-P

Have you looked at the Replace command:

Please Login or Register  to view this content.

Why do you need to replace? Can't you just set the value in the cells to PASS?

Also, check you myCopy string. The last entry B36 has a space in it, which may give you an error.

Also, when posting code, please use the CODE box. To do this surround your code with [ C O D E ] & [ / C O D E ] (minus the spaces).

Cheers, Rob.