Dynamic Name range with comparison of variables in column

**cceze** · 04-07-2012, 01:10 PM

Please I need help with this:

I have a defined table name with more that 9 columns (i have 4 columns for an example). I want the last column (column 4) to always compare variables in column 2 and 3 and populate (using VBA because i have about 40 columns in the original data). I used alot of IF Then statement with the Cell (i,j) to write the VBA

Everything worked but the problem is that when I add an additional column to the table, I have to keep manually changing the cell ranges (Cell (i,j)). Is there a way to make VBA know the column headers so that when I add a new column, I don't have to keep changing the range.

see attached example

Thanks!!!!!!

**dangelor** · 04-07-2012, 02:51 PM

One possibility is to create a named range of a cell in the first compare column and use it to calculate the starting column.

Sub Compare()
    Dim i As Long
    Dim iLastRow As Long
    Dim iName1Col As Long
    
    iLastRow = Range("A65536").End(xlUp).Row
    iName1Col = Range("Name1").Column 'assumes an existing named range
    For i = 1 To iLastRow
        If Cells(i, iName1Col).Value = "we" And _
           Cells(i, iName1Col + 1).Value = "nate" Then
            Cells(i, iName1Col + 2).Value = "good"
        End If
    Next i
End Sub