evaluate contents and select correct column

[Tim Miller]

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

 

[JW]

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

 

[JW]

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]

Thanks very much JW!

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