asked on

vba vlookup formula based on column heading?

I need to enter the formula in the last empty cell and copy down. However, I now realize that the E2 & M2 in my formula, which represents weight and a integer - may be in different columns! Can I find them based on the column header?? It will be the only thing certain to be the same. HELP!??

Sub GroundRating()
Application.ScreenUpdating = False
    Dim NextCol As Long, LastRow As Long
    With Filtered_Data
        NextCol = .Cells(1, Columns.Count).End(xlToLeft).Column + 1
        LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
        .Cells(1, NextCol) = "Ground Rates"
        .Range(.Cells(2, NextCol), .Cells(LastRow, NextCol)).Formula = "=IF($E2>=150,(VLOOKUP($E2,GR,$M2,TRUE)/150)*$E2,VLOOKUP($E2,GR,$M2,FALSE))"
    End With
    
    Application.ScreenUpdating = True
End Sub

Open in new window

sample-ground-formula.xlsx

Euro5

ASKER

This is what I'm trying now, but code stopping.

Sub GroundRating()
Application.ScreenUpdating = False
    Dim NextCol As Long, LastRow As Long
    With Filtered_Data
        NextCol = .Cells(1, Columns.Count).End(xlToLeft).Column + 1
        LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
        .Cells(1, NextCol) = "Ground Rates"
        .Range(.Cells(2, NextCol), .Cells(LastRow, NextCol)).Formula = "=IF(vlookup(MATCH(""Rated Weight"",$A$1:NextCol,0)>=150,VLOOKUP(MATCH(""Rated Weight"",$A$1:NextCol,0),GR,MATCH(""Zone"",$A$1:NextCol),TRUE)/150)*MATCH(""Rated Weight"",$A$1:NextCol,0),VLOOKUP(MATCH(""Rated Weight"",$A$1:NextCol,0),GR,MATCH(""Zone"",$A$1:NextCol,0),FALSE))"
    End With
    
    Application.ScreenUpdating = True
End Sub

Open in new window

ASKER CERTIFIED SOLUTION

Glenn Ray

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Euro5

ASKER

AHHHHH!!!!!! Bless you!!! :) Excellent solution!!

Glenn Ray

Glad I could help.

Note that I did not include any error handling: if either of your required columns are missing or misspelled, your formula will be in error.

Regards,
-Glenn

Euro5

ASKER

Understood. I have a previous code that names these columns, so that they will always be exactly the same.
Thanks for thinking of that!

Euro5

ASKER

I did have a problem that the range was too small, but I already closed the question, so I had to repost - in case you see that.
The range only went to column N, but the user may have more data - so I really need it dynamic.
But its a small fix...

[ fanpages ]

Slightly easier on the eye...

Sub GroundRating()

  Dim intColumn_Rated_Weight                            As Integer
  Dim intColumn_Zone                                    As Integer
  Dim NextCol                                           As Long
  Dim LastRow                                           As Long
  Dim strFormula                                        As String
  
  intColumn_Rated_Weight = IIf(IsError(Application.WorksheetFunction.Match("Rated Weight", Worksheets("Sheet1").Rows(1&), 0)), _
                               0, _
                               Application.WorksheetFunction.Match("Rated Weight", Worksheets("Sheet1").Rows(1&), 0))
                               
  intColumn_Zone = IIf(IsError(Application.WorksheetFunction.Match("Zone", Worksheets("Sheet1").Rows(1&), 0)), _
                       0, _
                       Application.WorksheetFunction.Match("Zone", Worksheets("Sheet1").Rows(1&), 0))
  
  If intColumn_Rated_Weight > 0 And _
     intColumn_Zone > 0 Then
     strFormula = Replace("=IF($E2>=150,(VLOOKUP($E2,GR,$M2,TRUE)/150)*$E2,VLOOKUP($E2,GR,$M2,FALSE))", _
                          "$E2", _
                          Cells(2&, intColumn_Rated_Weight).Address(RowAbsolute:=False))
                          
     strFormula = Replace(strFormula, _
                          "$M2", _
                          Cells(2&, intColumn_Zone).Address(RowAbsolute:=False))
     
     Application.ScreenUpdating = False
     
     With Filtered_Data
          NextCol = .Cells(1, Columns.Count).End(xlToLeft).Column + 1
          LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
         .Cells(1, NextCol) = "Ground Rates"
         .Range(.Cells(2, NextCol), .Cells(LastRow, NextCol)).Formula = strFormula
     End With
    
     Application.ScreenUpdating = True
  End If
  
End Sub

Open in new window