Exceptions focussed on 2 columns of data

[blacklabel]

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.

Thanks so much in advance.

 

[Andrew Poulsom]

Try:

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

 

[blacklabel]

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

 

[blacklabel]

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?

Thanks again in advance.

 

[blacklabel]

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.

Thanks in advance.

 

[blacklabel]

Wow, no one huh?

 

[blacklabel]

Anyone on today that can help me with this?