More efficient macro help

[Dean]

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

 

[Guest]

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

 

[Dean]

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

 

[Guest]

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

 

[Dean]

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