Automating Table Population

Posted by Kris Lyon on July 25, 2001 4:27 AM

I'm trying to automate filling in a table with results performed by a model. I need to insert a "1" into a cell which calculates through the model and produces a set of results. I then need to copy - paste value the results into a table. I need to repeat this 400 times starting with 1 in whole steps (1, 2, 3, etc).

I've done most of my automation with simple macros, but need help on this one to create the loop.

Can someone help?

Here's the basic procedure.

Thanks,

-Kris

Sheets("Rates & Pmts ").Select
Range("M5").Select
ActiveCell.FormulaR1C1 = "1"
Range("X7:AV7").Select
Selection.Copy
Sheets("Loan Terms Sheet").Select
Range("B6").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Posted by Kris Lyon on July 25, 2001 5:36 AM

OK - I think I'm getting a little closer. I created a test worksheet that takes the result (located in cell F7)of of my input (i) times 2 and places it in columns starting with I7 (r). I'm stuck on the (r). How do I get it to continue past the first offset?

Thanks for any help!

Sub Macro1()

i = 1
ni = i + 1
r = "I7"

For process = 1 To 5
Range("B7").Select
ActiveCell.FormulaR1C1 = i
Range("F7").Select
Selection.Copy
Range(r).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Next process
Range("B7").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = ni
Range("F7").Select
Selection.Copy
Range(r).Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

End Sub

Posted by Kris Lyon on July 25, 2001 7:41 AM

We'll I was able to get it to work with the following - any tips on improving the code?

Sub ShawnSpeedFill()

i = 1
ni = i + 1
r = "I7"

For i = 1 To 1 Step 1
Range("B7").Select
ActiveCell.FormulaR1C1 = i
Range("F7").Select
Selection.Copy
Range(r).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

For ni = 2 To 5 Step 1
Range("B7").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = ni
Range("F7").Select
Selection.Copy
Range(r).Offset(0, ni - 1).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Next ni
Next i

End Sub