Transferring from one workbook to another with exceptions

blacklabel

Board Regular
Joined
Aug 30, 2006
Messages
144
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.
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,606
Office Version
  1. 365
Platform
  1. Windows
blacklabel

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

blacklabel

Board Regular
Joined
Aug 30, 2006
Messages
144
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

Board Regular
Joined
Aug 30, 2006
Messages
144
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

Board Regular
Joined
Aug 30, 2006
Messages
144

ADVERTISEMENT

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

blacklabel

Board Regular
Joined
Aug 30, 2006
Messages
144
I hate to sound like a broken record here, but does anyone know how I can achieve this?
 

Watch MrExcel Video

Forum statistics

Threads
1,130,448
Messages
5,642,218
Members
417,262
Latest member
andrewd1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top