Index()Match() macro (using an absolute location)

[graybeam]

My macro code looks like this:

Sub FixCodedField()
'
' FixCodedField Macro
'
'
    Range(Cells(1, ActiveCell.Column), Cells(Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row, ActiveCell.Column)).Select
    Selection.Copy
    ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select
    Selection.Insert Shift:=xlToRight
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=INDEX(Sheet1!C[-12],MATCH(SBCLIENT!RC[-1],Sheet1!C[-13],0),1)"
    Range(Cells(2, ActiveCell.Column), Cells(Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row, ActiveCell.Column)).Select
    Selection.FillDown
    Selection.Copy
    ActiveCell.Offset(0, -1).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    ActiveCell.Offset(0, -1).Range("A1").Select
End Sub

The piece I'm most troubled with is

ActiveCell.FormulaR1C1 = _
        "=INDEX(Sheet1!C[-12],MATCH(SBCLIENT!RC[-1],Sheet1!C[-13],0),1)"

It seems using Relative References is becoming an issue. I have a column of data with a code in it like "19" or "2" and there is a key that turns that code into a meaning like "2 = NET30". I'm using the INDEX and MATCH functions as a sort of vlookup to match the values in my table (SBCLIENT) to the key I keep on another worksheet (Sheet1). The macro works great, if I don't try to use it on another column.

For some reason, I think it's relative references, when I put a different key into Sheet1 and run this macro on a different column location, it ruins my reference and lookup array and I get the #N/A error.

I'd like to get a macro going that is flexable enough to use in any column that will look up a key on another sheet and use it to replace the code values with the actual values.

Does that make any sense at all??? LOL!

 

[graybeam]

I fixed it with this:

Sub FixCodedFields()
'
' FixCodedFields Macro
'
'
    Range(Cells(1, ActiveCell.Column), Cells(Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row, ActiveCell.Column)).Select
    Selection.Copy
    ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select
    Selection.Insert Shift:=xlToRight
    ActiveCell.Offset(1, 0).Range("A1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=INDEX(Sheet1!C2,MATCH(SBCLIENT!RC[-1],Sheet1!C1,0),1)"
    Range(Cells(2, ActiveCell.Column), Cells(Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row, ActiveCell.Column)).Select
    Selection.FillDown
    Selection.Copy
    ActiveCell.Offset(0, -1).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    ActiveCell.Offset(0, -1).Columns("A:A").EntireColumn.Select
    Selection.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    ActiveCell.Select
End Sub

 

[hazza147]

Try using Named Range's. Even if it is only for 1 cell it is still useful. Means that if you insert a column or row in between your data points then it won't flag up as an error.

Cheers,
Harry