working by column name
 

I am trying to write some macros that operate using column headers instead
of column
identifiers such as A, B, etc. At the end of line 7, I should have a range
set to the entire
column that has "AZ slave" in row 1.

Line 8 is supposed to insert a blank column next to the column I just
selected.
This doesn't work. No column is inserted. When I look up
the insert function, the description never says what happens with the new
column.
It seems to be that I sould be able to insert it and attach it to the
variable
AZ_Slave_Mode_Column.
Line 9 is an alternative to 8 that also does not work.

Note: I can not get this stinking Microsoft Outlook to let me paste in text
without double
spacing it. I typed this in and it probably has some typos.


1 Sub Add_Slave_Mode_Columns()
2 Dim this_sheet As Worksheet
3 Dim AZ_Slave_Mode_Column As Range
4 Dim AZ_Slave_Mode_Column As Range
5 Dim AZ_Slave_Value_Column As Range

6 Set this_sheet = Worksheets("data")
7 Set AZ_Slave_Value_Column = Select_Column_By_Name("AZ slave", this_sheet)

8 Set AZ_Slave_Mode_Column = AZ_Slave_Value_Column.Insert(xlToRight)
9 AZ_Slave_Mode_Column =
Worksheets("data").Columns(AZ_Slave_Value_Column). Insert
End Sub


10 Function Select_Column_By_Name(find_target As String, w_s As Worksheet)
As Range
11 Dim caller_range As Range
12 Set caller_range = w_s.Range("A1:AZ1"). _
13 Find(find_target, _
14 Lookat := x1Whole,_
15 LookIn:=x1Values,_
16 MatchCase:=False)
' Extend the range down to the last row.
17 Set caller_range = w_s.Range(caller_range,caller_range.End(x1down))
18 Set Select_Column_By_Name = caller_range
19 End Function

Question: How do I insert a column such that after the insert I have my
range variable
AZ_Slave_Mode_Columnthat points to that column?

Question: Once I have that column, how do I do this:
AZ_Slave_Mode_Column.row_two_formula =
AZ_Mode_Column.row_two_value - Axis_2_Mode_Column.row_two_value.

Obviously the syntax is wrong, but I hope I have expressed the correct
concepts here.

Thank you,
Bryan

working by column name
 

Sub ABCD()
Dim rng As Range, rng1 As Range
Set rng = Range("F1:F20")
rng.Insert shift:=xlToRight
Set rng1 = rng.Offset(0, -1)
Debug.Print rng.Address, rng1.Address
End Sub

the insert method does not return a range object. Use offset with your
found range to get inserted column/cells.

--
Regards,
Tom Ogilvy

"Bryan" wrote in message
m...

I am trying to write some macros that operate using column headers instead
of column
identifiers such as A, B, etc. At the end of line 7, I should have a
range
set to the entire
column that has "AZ slave" in row 1.

Line 8 is supposed to insert a blank column next to the column I just
selected.
This doesn't work. No column is inserted. When I look up
the insert function, the description never says what happens with the new
column.
It seems to be that I sould be able to insert it and attach it to the
variable
AZ_Slave_Mode_Column.
Line 9 is an alternative to 8 that also does not work.

Note: I can not get this stinking Microsoft Outlook to let me paste in
text
without double
spacing it. I typed this in and it probably has some typos.


1 Sub Add_Slave_Mode_Columns()
2 Dim this_sheet As Worksheet
3 Dim AZ_Slave_Mode_Column As Range
4 Dim AZ_Slave_Mode_Column As Range
5 Dim AZ_Slave_Value_Column As Range

6 Set this_sheet = Worksheets("data")
7 Set AZ_Slave_Value_Column = Select_Column_By_Name("AZ slave",
this_sheet)

8 Set AZ_Slave_Mode_Column = AZ_Slave_Value_Column.Insert(xlToRight)
9 AZ_Slave_Mode_Column =
Worksheets("data").Columns(AZ_Slave_Value_Column). Insert
End Sub


10 Function Select_Column_By_Name(find_target As String, w_s As
Worksheet)
As Range
11 Dim caller_range As Range
12 Set caller_range = w_s.Range("A1:AZ1"). _
13 Find(find_target, _
14 Lookat := x1Whole,_
15 LookIn:=x1Values,_
16 MatchCase:=False)
' Extend the range down to the last row.
17 Set caller_range = w_s.Range(caller_range,caller_range.End(x1down))
18 Set Select_Column_By_Name = caller_range
19 End Function

Question: How do I insert a column such that after the insert I have my
range variable
AZ_Slave_Mode_Columnthat points to that column?

Question: Once I have that column, how do I do this:
AZ_Slave_Mode_Column.row_two_formula =
AZ_Mode_Column.row_two_value - Axis_2_Mode_Column.row_two_value.

Obviously the syntax is wrong, but I hope I have expressed the correct
concepts here.

Thank you,
Bryan