Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Flexible procedure
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 a 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Flexible procedure
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 wrote: 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 a 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Flexible procedure
Wow! That was fast. You know your Excel macros. I will start integrating
this tomorrow. Thank you, Bryan "Dave Peterson" wrote in message ... 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 wrote: 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 a 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
flexible find | Excel Discussion (Misc queries) | |||
I NEED A FLEXIBLE FORMULA | Excel Worksheet Functions | |||
Flexible Charts | Charts and Charting in Excel | |||
Calling a procedure in a procedure | Excel Programming |