More efficient macro help
 

Below are two iterations within my macro. Basically, it does this 14 times,
each time, changing the row reference by adding 9 more rows in the first
line - that's all the difference you will see in the two sets of macro text
below. Right now I have 14 of these, but I imagine I only need one, if I
somehow build a loop to add 9 rows and repeat. Can you help me with that?

Application.Goto Reference:="R7C15"
ActiveCell.Range("A1:A5").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents
ActiveCell.Offset(-1, -1).Range("A1:A6").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Table RowInput:=Range("P2"), ColumnInput:=Range("E12")

Application.Goto Reference:="R16C15"
ActiveCell.Range("A1:A5").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents
ActiveCell.Offset(-1, -1).Range("A1:A6").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Table RowInput:=Range("P2"), ColumnInput:=Range("E12")

Also, rather than making 10 copies of the above, I would like the
ColumnInput index parameter, currently "E12", for the data table to change
based on what text I have in a certain worksheet cell, say cell A1 (of the
current worksheet). For example, if cell a1 = "joe", rather than "jane",
then the column reference in the data table should be cell F10, not E12, and
so on for about 6 choices in cell A1. Can you also help me incorporate such
a variable cell reference? This is independent of what I've asked for in the
paragraph above. In other words, this data table columninput would be the
same for all 14 loops.

Thanks so much!
Dean

More efficient macro help
 

for i = 7 to 124 step 9
Application.Goto Reference:="R" & i & "C15"
ActiveCell.Range("A1:A5").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents
ActiveCell.Offset(-1, -1).Range("A1:A6").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Table RowInput:=Range("P2"), ColumnInput:=Range("E12")
Next i

--
Regards,
Tom Ogilvy

"Dean" wrote:

Below are two iterations within my macro. Basically, it does this 14 times,
each time, changing the row reference by adding 9 more rows in the first
line - that's all the difference you will see in the two sets of macro text
below. Right now I have 14 of these, but I imagine I only need one, if I
somehow build a loop to add 9 rows and repeat. Can you help me with that?

Application.Goto Reference:="R7C15"
ActiveCell.Range("A1:A5").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents
ActiveCell.Offset(-1, -1).Range("A1:A6").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Table RowInput:=Range("P2"), ColumnInput:=Range("E12")

Application.Goto Reference:="R16C15"
ActiveCell.Range("A1:A5").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents
ActiveCell.Offset(-1, -1).Range("A1:A6").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Table RowInput:=Range("P2"), ColumnInput:=Range("E12")

Also, rather than making 10 copies of the above, I would like the
ColumnInput index parameter, currently "E12", for the data table to change
based on what text I have in a certain worksheet cell, say cell A1 (of the
current worksheet). For example, if cell a1 = "joe", rather than "jane",
then the column reference in the data table should be cell F10, not E12, and
so on for about 6 choices in cell A1. Can you also help me incorporate such
a variable cell reference? This is independent of what I've asked for in the
paragraph above. In other words, this data table columninput would be the
same for all 14 loops.

Thanks so much!
Dean

More efficient macro help
 

That works great except we forgot to dimension i, I assume as an integer.
Can you also help with the 2nd part of my request? I put it below the macro
I copied in. I know this stuff should be easy. I'm just not that
comfortable with the VBA syntax yet.

Thanks!
Dean

"Tom Ogilvy" wrote in message
...
for i = 7 to 124 step 9
Application.Goto Reference:="R" & i & "C15"
ActiveCell.Range("A1:A5").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents
ActiveCell.Offset(-1, -1).Range("A1:A6").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Table RowInput:=Range("P2"), ColumnInput:=Range("E12")
Next i

--
Regards,
Tom Ogilvy

"Dean" wrote:

Below are two iterations within my macro. Basically, it does this 14
times,
each time, changing the row reference by adding 9 more rows in the first
line - that's all the difference you will see in the two sets of macro
text
below. Right now I have 14 of these, but I imagine I only need one, if I
somehow build a loop to add 9 rows and repeat. Can you help me with
that?

Application.Goto Reference:="R7C15"
ActiveCell.Range("A1:A5").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents
ActiveCell.Offset(-1, -1).Range("A1:A6").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Table RowInput:=Range("P2"), ColumnInput:=Range("E12")

Application.Goto Reference:="R16C15"
ActiveCell.Range("A1:A5").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents
ActiveCell.Offset(-1, -1).Range("A1:A6").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Table RowInput:=Range("P2"), ColumnInput:=Range("E12")

Also, rather than making 10 copies of the above, I would like the
ColumnInput index parameter, currently "E12", for the data table to
change
based on what text I have in a certain worksheet cell, say cell A1 (of
the
current worksheet). For example, if cell a1 = "joe", rather than "jane",
then the column reference in the data table should be cell F10, not E12,
and
so on for about 6 choices in cell A1. Can you also help me incorporate
such
a variable cell reference? This is independent of what I've asked for in
the
paragraph above. In other words, this data table columninput would be
the
same for all 14 loops.

Thanks so much!
Dean

More efficient macro help
 

we forgot to dimension i
should read Dean forgot to dimension i



Dim i as Long
Dim rng as Range
if len(trim(Range("A1"))) = 0 then exit sub
Select Case lcase(Range("A1"))
Case "jane"
set rng = range("E12")
Case "joe"
set rng = Range("F10")
Case "moe"
set rng = Range("G8")
Case "curly"
set rng = Range("H6")
Case "bozo"
set rng = Range("I4")
Case "harpo"
set rng = Range("j2")
end Select
for i = 7 to 124 step 9
Application.Goto Reference:="R" & i & "C15"
ActiveCell.Range("A1:A5").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents
ActiveCell.Offset(-1, -1).Range("A1:A6").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Table RowInput:=Range("P2"), ColumnInput:=rng
Next i

--
Regards,
Tom Ogilvy


"Dean" wrote:

That works great except we forgot to dimension i, I assume as an integer.
Can you also help with the 2nd part of my request? I put it below the macro
I copied in. I know this stuff should be easy. I'm just not that
comfortable with the VBA syntax yet.

Thanks!
Dean

"Tom Ogilvy" wrote in message
...
for i = 7 to 124 step 9
Application.Goto Reference:="R" & i & "C15"
ActiveCell.Range("A1:A5").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents
ActiveCell.Offset(-1, -1).Range("A1:A6").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Table RowInput:=Range("P2"), ColumnInput:=Range("E12")
Next i

--
Regards,
Tom Ogilvy

"Dean" wrote:

Below are two iterations within my macro. Basically, it does this 14
times,
each time, changing the row reference by adding 9 more rows in the first
line - that's all the difference you will see in the two sets of macro
text
below. Right now I have 14 of these, but I imagine I only need one, if I
somehow build a loop to add 9 rows and repeat. Can you help me with
that?

Application.Goto Reference:="R7C15"
ActiveCell.Range("A1:A5").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents
ActiveCell.Offset(-1, -1).Range("A1:A6").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Table RowInput:=Range("P2"), ColumnInput:=Range("E12")

Application.Goto Reference:="R16C15"
ActiveCell.Range("A1:A5").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents
ActiveCell.Offset(-1, -1).Range("A1:A6").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Table RowInput:=Range("P2"), ColumnInput:=Range("E12")

Also, rather than making 10 copies of the above, I would like the
ColumnInput index parameter, currently "E12", for the data table to
change
based on what text I have in a certain worksheet cell, say cell A1 (of
the
current worksheet). For example, if cell a1 = "joe", rather than "jane",
then the column reference in the data table should be cell F10, not E12,
and
so on for about 6 choices in cell A1. Can you also help me incorporate
such
a variable cell reference? This is independent of what I've asked for in
the
paragraph above. In other words, this data table columninput would be
the
same for all 14 loops.

Thanks so much!
Dean

More efficient macro help
 

Thanks very much, Tom. This will keep me from looking like the rookie I am!

"Tom Ogilvy" wrote in message
...
we forgot to dimension i
should read Dean forgot to dimension i



Dim i as Long
Dim rng as Range
if len(trim(Range("A1"))) = 0 then exit sub
Select Case lcase(Range("A1"))
Case "jane"
set rng = range("E12")
Case "joe"
set rng = Range("F10")
Case "moe"
set rng = Range("G8")
Case "curly"
set rng = Range("H6")
Case "bozo"
set rng = Range("I4")
Case "harpo"
set rng = Range("j2")
end Select
for i = 7 to 124 step 9
Application.Goto Reference:="R" & i & "C15"
ActiveCell.Range("A1:A5").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents
ActiveCell.Offset(-1, -1).Range("A1:A6").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Table RowInput:=Range("P2"), ColumnInput:=rng
Next i

--
Regards,
Tom Ogilvy


"Dean" wrote:

That works great except we forgot to dimension i, I assume as an integer.
Can you also help with the 2nd part of my request? I put it below the
macro
I copied in. I know this stuff should be easy. I'm just not that
comfortable with the VBA syntax yet.

Thanks!
Dean

"Tom Ogilvy" wrote in message
...
for i = 7 to 124 step 9
Application.Goto Reference:="R" & i & "C15"
ActiveCell.Range("A1:A5").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents
ActiveCell.Offset(-1, -1).Range("A1:A6").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Table RowInput:=Range("P2"), ColumnInput:=Range("E12")
Next i

--
Regards,
Tom Ogilvy

"Dean" wrote:

Below are two iterations within my macro. Basically, it does this 14
times,
each time, changing the row reference by adding 9 more rows in the
first
line - that's all the difference you will see in the two sets of macro
text
below. Right now I have 14 of these, but I imagine I only need one,
if I
somehow build a loop to add 9 rows and repeat. Can you help me with
that?

Application.Goto Reference:="R7C15"
ActiveCell.Range("A1:A5").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents
ActiveCell.Offset(-1, -1).Range("A1:A6").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Table RowInput:=Range("P2"), ColumnInput:=Range("E12")

Application.Goto Reference:="R16C15"
ActiveCell.Range("A1:A5").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents
ActiveCell.Offset(-1, -1).Range("A1:A6").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Table RowInput:=Range("P2"), ColumnInput:=Range("E12")

Also, rather than making 10 copies of the above, I would like the
ColumnInput index parameter, currently "E12", for the data table to
change
based on what text I have in a certain worksheet cell, say cell A1 (of
the
current worksheet). For example, if cell a1 = "joe", rather than
"jane",
then the column reference in the data table should be cell F10, not
E12,
and
so on for about 6 choices in cell A1. Can you also help me
incorporate
such
a variable cell reference? This is independent of what I've asked for
in
the
paragraph above. In other words, this data table columninput would be
the
same for all 14 loops.

Thanks so much!
Dean