Flexible procedure

[Bryan]

This is a two part question:
Part 1: The question is a bit difficult to phrase clearly. If I have not
asked the question well, please tell me. I simplified this greatly and hope
I made no types in the simplification.

Part 2:
This is part of a procedure that will add a column to a data sheet. That
column will calculate its value based on two other columns. However, the
two other columns may change position from time to time. So far the
statements of interest are:

Dim Temp_Column As Range
Dim data_sheet As Worksheet
Dim Status_Column_Number As Integer
Dim Mode_Column_Number As Integer

' The sheet containing the data will always be named "data"
Set data_sheet = Worksheets("data")

' The next line uses a function that will return a column as a range.
That column will be
' the column that is headed with "Status".
' The following line gets the number of the column.
Set Temp_Column = Select_Column_By_Name("Status", data_sheet)
Status_Column_Number = Temp_Column.Column

Set Temp_Column = Select_Column_By_Name("Mode", data_sheet)
Mode_Column_Number = Temp_Column.Column

At this point lest assume I have
Status_Column_Number = 2
Mode_Column_Number = 3

The next statement I want to do is this:

AA2.Formula = "=B2 - C2"

I want to set the formula in the cell of row 2 column AA to "B2 - C2"
The actual statement is more complicated but this will serve. How do build
the string "=B2 - C2" from the knowledge that I have the numbers 2 and 3
that represent columns B and C?

Thanks for your time,
Bryan

 

[Dave Peterson]

How about:

With data_sheet
.Range("aa2").Formula = "=" & .Cells(2, status_column_number).Address(0, 0) _
& "-" & .Cells(2, mode_column_number).Address(0, 0)
End With

 

[Bryan]

Wow! That was fast. You know your Excel macros. I will start integrating
this tomorrow.
Thank you,
Bryan