Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
evaluate contents and select correct column
Below I have a little macro that executes a program with the '/project X'
switch, where X is equal to the value in column D of the active row. Is there simple modification that will: A: Evaluate all of row #1, find the cell called "Project_ID", and then use that cell from the active row, rather then just always using D. So if the project_id is in column B, and I'm on row 100, then it would pass the value from b100 to my switch. B: Make it so that if I have multiple worksheets and the project_id moves around, it responds correctly. That is, Worksheet1 has project_id in column B, but worksheet2 has it in column D... either way pressing, say CTRL-L, grabs the correct value. And then if I insert a column I don't have to modify the macro... it just finds project_id in it's new location. I think this is just a matter of taking the bit that defines 'column_with_switch_data' and moving it into the sub. However I'm not sure if I can have one for the entire workbook, or if I have to have a macro defined per worksheet. An acceptable alternative is to just fix the project_id to the same column for all, but it'd be nice to have this greater flexibility. I've just been unable to find the tweaks to make that happen and can't justify any more time spent on it. Const COLUMN_WITH_SWITCH_DATA = "D" --- Sub OpenPTS() Shell "C:\Program Files\Project Tracking System\ProjectTrackingSystem.exe" & " /project " & ActiveCell.EntireRow.Cells(1, COLUMN_WITH_SWITCH_DATA).Text, vbNormalFocus End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
evaluate contents and select correct column
This will find "Project_ID" in row 1 and return it's column number.
That number can then be used in your Cells statement to find the right position. Sub foo() Dim COLUMN_WITH_SWITCH_DATA As Integer COLUMN_WITH_SWITCH_DATA = _ Rows("1:1").Find(What:="Project_ID", After:=Range("A1"), LookIn:=xlFormulas _ , LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False).Column End Sub Tim Miller wrote: Below I have a little macro that executes a program with the '/project X' switch, where X is equal to the value in column D of the active row. Is there simple modification that will: A: Evaluate all of row #1, find the cell called "Project_ID", and then use that cell from the active row, rather then just always using D. So if the project_id is in column B, and I'm on row 100, then it would pass the value from b100 to my switch. B: Make it so that if I have multiple worksheets and the project_id moves around, it responds correctly. That is, Worksheet1 has project_id in column B, but worksheet2 has it in column D... either way pressing, say CTRL-L, grabs the correct value. And then if I insert a column I don't have to modify the macro... it just finds project_id in it's new location. I think this is just a matter of taking the bit that defines 'column_with_switch_data' and moving it into the sub. However I'm not sure if I can have one for the entire workbook, or if I have to have a macro defined per worksheet. An acceptable alternative is to just fix the project_id to the same column for all, but it'd be nice to have this greater flexibility. I've just been unable to find the tweaks to make that happen and can't justify any more time spent on it. Const COLUMN_WITH_SWITCH_DATA = "D" --- Sub OpenPTS() Shell "C:\Program Files\Project Tracking System\ProjectTrackingSystem.exe" & " /project " & ActiveCell.EntireRow.Cells(1, COLUMN_WITH_SWITCH_DATA).Text, vbNormalFocus End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
evaluate contents and select correct column
Here's the complete code I would use to do what I believe you are
wanting. Sub foo() Dim COLUMN_WITH_SWITCH_DATA As Integer COLUMN_WITH_SWITCH_DATA = _ Rows("1:1").Find(What:="Project_ID", After:=Range("A1"), LookIn:=xlFormulas _ , LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False).Column Shell "C:\Program Files\Project Tracking System \ProjectTrackingSystem.exe" & _ " /project " & Cells(ActiveCell.Row, COLUMN_WITH_SWITCH_DATA).Text, vbNormalFocus MsgBox End Sub Tim Miller wrote: Below I have a little macro that executes a program with the '/project X' switch, where X is equal to the value in column D of the active row. Is there simple modification that will: A: Evaluate all of row #1, find the cell called "Project_ID", and then use that cell from the active row, rather then just always using D. So if the project_id is in column B, and I'm on row 100, then it would pass the value from b100 to my switch. B: Make it so that if I have multiple worksheets and the project_id moves around, it responds correctly. That is, Worksheet1 has project_id in column B, but worksheet2 has it in column D... either way pressing, say CTRL-L, grabs the correct value. And then if I insert a column I don't have to modify the macro... it just finds project_id in it's new location. I think this is just a matter of taking the bit that defines 'column_with_switch_data' and moving it into the sub. However I'm not sure if I can have one for the entire workbook, or if I have to have a macro defined per worksheet. An acceptable alternative is to just fix the project_id to the same column for all, but it'd be nice to have this greater flexibility. I've just been unable to find the tweaks to make that happen and can't justify any more time spent on it. Const COLUMN_WITH_SWITCH_DATA = "D" --- Sub OpenPTS() Shell "C:\Program Files\Project Tracking System\ProjectTrackingSystem.exe" & " /project " & ActiveCell.EntireRow.Cells(1, COLUMN_WITH_SWITCH_DATA).Text, vbNormalFocus End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
evaluate contents and select correct column
Thanks very much JW!
"JW" wrote in message ps.com... Here's the complete code I would use to do what I believe you are wanting. Sub foo() Dim COLUMN_WITH_SWITCH_DATA As Integer COLUMN_WITH_SWITCH_DATA = _ Rows("1:1").Find(What:="Project_ID", After:=Range("A1"), LookIn:=xlFormulas _ , LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False).Column Shell "C:\Program Files\Project Tracking System \ProjectTrackingSystem.exe" & _ " /project " & Cells(ActiveCell.Row, COLUMN_WITH_SWITCH_DATA).Text, vbNormalFocus MsgBox End Sub Tim Miller wrote: Below I have a little macro that executes a program with the '/project X' switch, where X is equal to the value in column D of the active row. Is there simple modification that will: A: Evaluate all of row #1, find the cell called "Project_ID", and then use that cell from the active row, rather then just always using D. So if the project_id is in column B, and I'm on row 100, then it would pass the value from b100 to my switch. B: Make it so that if I have multiple worksheets and the project_id moves around, it responds correctly. That is, Worksheet1 has project_id in column B, but worksheet2 has it in column D... either way pressing, say CTRL-L, grabs the correct value. And then if I insert a column I don't have to modify the macro... it just finds project_id in it's new location. I think this is just a matter of taking the bit that defines 'column_with_switch_data' and moving it into the sub. However I'm not sure if I can have one for the entire workbook, or if I have to have a macro defined per worksheet. An acceptable alternative is to just fix the project_id to the same column for all, but it'd be nice to have this greater flexibility. I've just been unable to find the tweaks to make that happen and can't justify any more time spent on it. Const COLUMN_WITH_SWITCH_DATA = "D" --- Sub OpenPTS() Shell "C:\Program Files\Project Tracking System\ProjectTrackingSystem.exe" & " /project " & ActiveCell.EntireRow.Cells(1, COLUMN_WITH_SWITCH_DATA).Text, vbNormalFocus End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Challenge - evaluate and select | Excel Discussion (Misc queries) | |||
Evaluate a column and extract last value | Excel Worksheet Functions | |||
Select Case Statement does not evaluate | Excel Programming | |||
How do I select the correct record? | Excel Discussion (Misc queries) | |||
Programming Help - evaluate, select and Past into new workbook | Excel Programming |