Transferring from one workbook to another with exceptions

[blacklabel]

I posted this a while ago with no replies so maybe I wasn't clear enough on what I was trying to do.

I have 2 workbooks: "Rejects" and "Nearest Zip Code". I'm trying to replace a "#N/A" value in Column H from the "Rejects" workbook based on the "Nearest Zip Code". The statement I need is if Column H in "Rejects" is "#N/A" then replace the cell value with Column H in "Nearest Zip Code" only when Column G in both workbooks are the same. I hope that makes sense. If anyone can help me with this in any way, I would appreciate it. I can offer an example if needed. I really need some help with this.

Thanks in advance.

 

[Peter_SSs]

blacklabel

Can you post an example of the formula that is returning the #N/A?

 

[blacklabel]

It's part of a vlookup in which returns the #N/A value when a cell is blank. All I need to do is go back and replace the #N/A value with any number in Column H, as long as the values in G for both workbooks are the same. Here's an example:

Rejects Workbook
G H
1 #N/A
1 #N/A
1 #N/A

Nearest Zip Code Workbook
G H
1 23
1 23
2 24

The#N/A values in the Rejects workbook would be changed to 23. I hope that helps. The vlookup wouldn't help very much since a column is deleted after it's run, but I can post it if you need it. Let me know if you need anything else.

 

[blacklabel]

Here's the vlookup, just in case it might be helpful:

Workbooks.Open Filename:= _
"C:\Nearest Zip Code.xls"
Windows("Rejects.TXT").Activate
Range("H2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[Nearest Zip Code.xls]ZIP2CTR'!C6:C7,2,FALSE)"
Selection.AutoFill Destination:=Range("H2:H65536"), Type:=xlFillDefault
Range("H2:H65536").Select
Columns("H:H").EntireColumn.AutoFit
ActiveWindow.ScrollRow = 65536

 

[blacklabel]

Is there anyone else that can help me out with this?

 

[blacklabel]

Is this done with a vlookup as well? If so, how? Anyone?

 

[blacklabel]

I hate to sound like a broken record here, but does anyone know how I can achieve this?