Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
more efficient VBA? | Excel Programming | |||
Is there a more efficient way to do this? | Excel Programming | |||
VBA - Efficient Macro | Excel Programming | |||
More efficient way? | Excel Programming | |||
Which is more efficient? | Excel Programming |