Refer to cells in formula using column headers

suremac

New Member
Joined
Jan 27, 2014
Messages
49
The problem is that in other versions of Sheet1, a sample of the worksheet data is provided in the image below, the data in columns C through F may be in different columns (e.g the data in column A in version 1 of Sheet1 may be in column X in version 2 of Sheet1). I'm trying to make the macro more flexible by having the formulas in the macro somehow refer to the relevant cell by using the column name (i.e. the names in row 1), instead of using only R1C1 notation.
Here is my code:

Code:
Sub Formulas()
Dim mycell As Range
With Sheets("Sheet1")
.Range("A1").EntireColumn.Insert
.Range("A1").EntireColumn.Insert
.Cells(1, 1) = "Indicator3 >= 2"
.Cells(1, 2) = "True Failure"
lr = Cells(Rows.Count, 3).End(xlUp).Row
.Range(.Cells(2, 1), .Cells(lr, 1)).FormulaR1C1 = "=IF(AND(RC[4]=""F"",(AND(ISNUMBER(SEARCH(""ECONOMICS"",R[1]C[3])),R[1]C[4]=""P"")),(ISNUMBER(R[1]C[5]))),R[1]C[5]-RC[5],0)"
.Range(.Cells(2, 2), .Cells(lr, 2)).FormulaR1C1 = "=IF(AND(RC[3]=""F"",NOT(AND(ISNUMBER(SEARCH(""ECONOMICS"",R[1]C[2])),R[1]C[3]=""P"")),(ISNUMBER(RC[4]))),1,0)"
End With
End Sub


hYrF0.png


Any help is much appreciated.

Regards,
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Hi suremac,

You could use Application.Match to get the column number associated with each header, then use those values in your formulas.

This example uses Absolute references for the columns instead of Relative to make the notation simpler. If you need relative you can add the brackets and subtract 1 from each Column number.

Code:
Sub Formulas()
 Dim lColInd1 As Long, lColInd2 As Long, lColInd3 As Long, lr As Long

 With Sheets("Sheet1")
   .Range("A1:B1").EntireColumn.Insert
   .Cells(1, 1) = "Indicator3 >= 2"
   .Cells(1, 2) = "True Failure"
   lr = .Cells(Rows.Count, 3).End(xlUp).Row

   lColInd1 = Application.Match("Indicator1", .Rows(1), 0)
   lColInd2 = Application.Match("Indicator2", .Rows(1), 0)
   lColInd3 = Application.Match("Indicator3", .Rows(1), 0)
   
   .Range(.Cells(2, 1), .Cells(lr, 1)).FormulaR1C1 = _
      "=IF(AND(RC" & lColInd2 & "=""F"",(AND(ISNUMBER(SEARCH(""ECONOMICS""," & _
      "R[1]C" & lColInd1 & ")),R[1]C" & lColInd2 & "=""P"")),(ISNUMBER(R[1]C" & _
      lColInd3 & "))),R[1]C" & lColInd2 & "-RC" & lColInd3 & ",0)"

 End With
 
End Sub
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,628
Messages
5,838,452
Members
430,549
Latest member
jayjay2022

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top