I got it working a different way.. I put the VRM list in the same file
set it as hidden (in VBEditor), password protected the VBEditor an
used the following code to do what I wanted to do:

Sub VRMReplace()

Dim MyStr1 As String, MyStr2 As String
MyStr2 = ("mypassword") '*****This is the password
MyStr1 = InputBox("Please Enter Password")
If MyStr1 = MyStr2 Then

Dim cel As Range, rng1 As Range, rng2 As Range, rng3 As Range

Set rng1 = Worksheets("Venture").Range("A22:A58")
Set rng2 = Worksheets("VRM").Range("B1:C145")
Set rng3 = Worksheets("VRM").Range("B:B")
Set rng4 = Worksheets("Industrial Grain").Range("A22:A25")
Set rng5 = Worksheets("Receiving").Range("A8:A43")
Set rng6 = Worksheets("Manhours").Range("A24:A30")

For Each cel In rng1
If WorksheetFunction.CountIf(rng3, cel) 0 Then
cel = WorksheetFunction.VLookup(cel, rng2, 2, False)
Else
MsgBox "Product Code not found"
End If
Next

For Each cel In rng4
If WorksheetFunction.CountIf(rng3, cel) 0 Then
cel = WorksheetFunction.VLookup(cel, rng2, 2, False)
Else
MsgBox "Product Code not found"
End If
Next

For Each cel In rng5
If WorksheetFunction.CountIf(rng3, cel) 0 Then
cel = WorksheetFunction.VLookup(cel, rng2, 2, False)
Else
MsgBox "Product Code not found"
End If
Next

For Each cel In rng6
If WorksheetFunction.CountIf(rng3, cel) 0 Then
cel = WorksheetFunction.VLookup(cel, rng2, 2, False)
Else
MsgBox "Product Code not found"
End If
Next

ActiveWorkbook.PrintOut

ActiveWorkbook.Close False

Else
MsgBox ("Access Denied")
End If


End Su

--
richinlaf3
-----------------------------------------------------------------------
richinlaf31's Profile: http://www.excelforum.com/member.php...fo&userid=2584
View this thread: http://www.excelforum.com/showthread.php?threadid=39253

Looks pretty good!

A couple of questions.
1. Do you really want the message boxes? or just a different action...
2. After all the work is done - it looks like you are printing out the
results and
closing the workbook without saving it....

--
steveB

Remove "AYN" from email to respond
"richinlaf31"
wrote in message
...

I got it working a different way.. I put the VRM list in the same file,
set it as hidden (in VBEditor), password protected the VBEditor and
used the following code to do what I wanted to do:

Sub VRMReplace()

Dim MyStr1 As String, MyStr2 As String
MyStr2 = ("mypassword") '*****This is the password
MyStr1 = InputBox("Please Enter Password")
If MyStr1 = MyStr2 Then

Dim cel As Range, rng1 As Range, rng2 As Range, rng3 As Range

Set rng1 = Worksheets("Venture").Range("A22:A58")
Set rng2 = Worksheets("VRM").Range("B1:C145")
Set rng3 = Worksheets("VRM").Range("B:B")
Set rng4 = Worksheets("Industrial Grain").Range("A22:A25")
Set rng5 = Worksheets("Receiving").Range("A8:A43")
Set rng6 = Worksheets("Manhours").Range("A24:A30")

For Each cel In rng1
If WorksheetFunction.CountIf(rng3, cel) 0 Then
cel = WorksheetFunction.VLookup(cel, rng2, 2, False)
Else
MsgBox "Product Code not found"
End If
Next

For Each cel In rng4
If WorksheetFunction.CountIf(rng3, cel) 0 Then
cel = WorksheetFunction.VLookup(cel, rng2, 2, False)
Else
MsgBox "Product Code not found"
End If
Next

For Each cel In rng5
If WorksheetFunction.CountIf(rng3, cel) 0 Then
cel = WorksheetFunction.VLookup(cel, rng2, 2, False)
Else
MsgBox "Product Code not found"
End If
Next

For Each cel In rng6
If WorksheetFunction.CountIf(rng3, cel) 0 Then
cel = WorksheetFunction.VLookup(cel, rng2, 2, False)
Else
MsgBox "Product Code not found"
End If
Next

ActiveWorkbook.PrintOut

ActiveWorkbook.Close False

Else
MsgBox ("Access Denied")
End If


End Sub


--
richinlaf31
------------------------------------------------------------------------
richinlaf31's Profile:
http://www.excelforum.com/member.php...o&userid=25846
View this thread: http://www.excelforum.com/showthread...hreadid=392531