# Exceptions focussed on 2 columns of data

#### blacklabel

##### Board Regular
I'm just joined the board today. I looked around for a day to make sure I wasn't repeating any issue already discussed. Here goes:

I have an Excel document in which consists of columns A-R. Columns G and H are what I'm trying to manipulate in a macro. The explanation I need to define in code is If Column H=#N/A then replace the #N/A with the previous cell in column H that is a number, but only if the number in column G is the same. I'll put an example:

G H
1 10
1 10
1 #N/A
2 20

The #N/A would need to be automatically changed to 10 since that's what the previous cell is equal to in that column. If the value in Column G is not the same in the previous cell, then I need the macro to do nothing. I hope I explained that enough. If there's any confusion please let me know. I have a couple of more issues with this situation but I wanted to take them one step at a time.

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Try:

Code:
``````Sub Test()
Dim Sh As Worksheet
Dim Rng As Range
Dim Cell As Range
Set Sh = Worksheets("Sheet1")
Set Rng = Sh.Range("H:H").SpecialCells(xlCellTypeConstants, xlErrors)
For Each Cell In Rng
If Cell.Value = CVErr(xlErrNA) Then
If Cell.Offset(0, -1).Value = Cell.Offset(-1, -1).Value Then
Cell.Value = Cell.Offset(-1, 0).Value
End If
End If
Next Cell
End Sub``````

Great. It looks good so far. The next step would be the same issue, but replacing the #N/A with a blank cell.

Also, is there a way to combine replacing the #N/A with the previous cell or the next cell if Column G is the same? Is there a way to say that in one statement? Also how would I go about doing the same procedure if Column H is blank? Would I have to have two statements or can I have them look for either in one statement?

Ok, I figured out that problem. Now I have the final issue I'm dealing with and I should be set. I really hope someone can help me. Here goes:

The remaining values in column H that have #N/A as a value do not meet the statement listed above. What I need to do is automatically open a file that exists at lists the cooresponding number. Does that make sense? Let me give an example:

G H
1 #N/A
1 123
2 234
2 234

The file used as a reference is opened manually. The number in column G is referenced on this file and any number, as long as it cooresponds with the number in Column G, is placed in Column H with the file I'm actually working with. Now, if that number in Column H is used more than 10 times, I find the same number in Column G in the reference file and take the next number and replace it in Column G in the file I'm working with. Column G can have over 100 of the same number in the reference file I use but different cooresponding numbers in Column H. What I'm trying to do is automate this whole process.

If anyone can figure this on out, you deserve a medal.

Wow, no one huh?

Anyone on today that can help me with this?

Replies
10
Views
346
Replies
26
Views
828
Replies
0
Views
238
Replies
1
Views
152
Replies
1
Views
183

1,203,686
Messages
6,056,736
Members
444,887
Latest member
cvcc_wt

### 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.

### Which adblocker are you using?

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

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