Using for loops when referencing

**Operator** · 08-20-2009, 11:34 AM

First of all, I'm completely new to both this forum and VBA. I have just done a programming course in java before. I hope you will forgive me if this have already been posted. No to my problem.

I am tryring to use different projected values to project other subparts of those units, which is done in the sheet mean needed weekly. Tje values are taken from projections. The answer that I get from mean needed weekly should be copied to a third sheet.

This is the code that I am using right know, but I cant get the for loop to work (or more exactly to be able to place the "i" in my text without making compilation errors).

Sub Macro3()
'
' Macro3
' Macro recorded 8/20/2009 by XXX
Dim i As Integer

'
For i = 0 To 51 Step 1
'
    Sheets("Mean Needed Weekly").Select
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "=Projections!R[2]C[-3]" ' change into b5, b6 (   R[3]C[-3]   )etc. why does not i work?
    Range("E3").Select
    ActiveCell.FormulaR1C1 = "=Projections!R[1]C[-2]"
    Range("E4").Select
    ActiveCell.FormulaR1C1 = "=Projections!R[0]C[-1]"
    Range("E5").Select
    ActiveCell.FormulaR1C1 = "=Projections!R[-1]C"
    Range("E6").Select
    ActiveCell.FormulaR1C1 = "=Projections!R[-2]C[1]"
    Range("E7").Select
    ActiveCell.FormulaR1C1 = "=Projections!R[-3]C[2]"
    Range("E8").Select
    ActiveCell.FormulaR1C1 = "=Projections!R[-4]C[3]"
    Range("E9").Select
    ActiveCell.FormulaR1C1 = "=Projections!R[-5]C[4]"
    Range("E10").Select
    ActiveCell.FormulaR1C1 = "=Projections!R[-6]C[5]"
    Range("E11").Select
    ActiveCell.FormulaR1C1 = "=Projections!R[-7]C[6]"
    Range("E12").Select
    ActiveCell.FormulaR1C1 = "=Projections!R[-8]C[7]"
    Range("E13").Select
    ActiveCell.FormulaR1C1 = "=Projections!R[-9]C[8]"
    Range("E14").Select
    
    Range("M17:M340").Select
    Selection.Copy
    Sheets("Sheet1").Select
    Range((2), (i + 3)).Select ' change to c3, c4 etc. why does not i work?
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.SmallScroll Down:=291
    
    Next i
    
 

End Sub

Thanks in advance!

E

**shg** · 08-20-2009, 11:50 AM

Welcome to the forum, Operator.

Please take a few minutes to read the forum rules, and then edit your post to add code tags.

Thanks.

**Operator** · 08-20-2009, 12:10 PM

Is it other ways to make the code easier to read?

**shg** · 08-20-2009, 12:41 PM

Thanks.

What is this line supposed to do?

Range((2), (i + 3)).Select

**Operator** · 08-20-2009, 02:10 PM

What is this line supposed to do?

Code:
Range((2), (i + 3)).Select

Its suppose to decide where to paste the selected values (next line.)
When you paste something in excel the first cell decides where the rest of the values will be pasted.
As I already have selected which values to copy

Range("M17:M340").Select
    Selection.Copy

I just need to paste them somewhere. The line you asking for decides the place where to (and it works fine if the i+3 is changed into 3).
I want it to post the next values from the next i a column to left of the earlier ones. That's why I tried the for loop.

i+3 =4 but (when i =1

Range((2), (i + 3)).Select[/QUOTE]

but the i in the expression leads to error messages when I execute.

**shg** · 08-20-2009, 02:30 PM

The syntax is wrong.

What cell are you tring to select? What is the loop supposed to do?

Part of the macros can be simplified like this:

Sub Macro3()
    Dim i           As Long

    Sheets("Mean Needed Weekly").Select
    Range("E2").FormulaR1C1 = "=Projections!R[2]C[-3]"
    Range("E3").FormulaR1C1 = "=Projections!R[1]C[-2]"
    Range("E4").FormulaR1C1 = "=Projections!R[0]C[-1]"
    Range("E5").FormulaR1C1 = "=Projections!R[-1]C"
    Range("E6").FormulaR1C1 = "=Projections!R[-2]C[1]"
    Range("E7").FormulaR1C1 = "=Projections!R[-3]C[2]"
    Range("E8").FormulaR1C1 = "=Projections!R[-4]C[3]"
    Range("E9").FormulaR1C1 = "=Projections!R[-5]C[4]"
    Range("E10").FormulaR1C1 = "=Projections!R[-6]C[5]"
    Range("E11").FormulaR1C1 = "=Projections!R[-7]C[6]"
    Range("E12").FormulaR1C1 = "=Projections!R[-8]C[7]"
    Range("E13").FormulaR1C1 = "=Projections!R[-9]C[8]"

    Range("M17:M340").Copy
    Sheets("Sheet1").Select

    For i = 0 To 51 Step 1
        Range((2), (i + 3)).Select    ' This is wrong
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                               SkipBlanks:=False, Transpose:=False
    Next i
End Sub

**Operator** · 08-20-2009, 03:30 PM

Okay, the first part selects the input for a sheet that I have made:

Sheets("Mean Needed Weekly").Select
    Range("E2").FormulaR1C1 = "=Projections!R[2]C[-3]"
    Range("E3").FormulaR1C1 = "=Projections!R[1]C[-2]"
    Range("E4").FormulaR1C1 = "=Projections!R[0]C[-1]"
    Range("E5").FormulaR1C1 = "=Projections!R[-1]C"
    Range("E6").FormulaR1C1 = "=Projections!R[-2]C[1]"
    Range("E7").FormulaR1C1 = "=Projections!R[-3]C[2]"
    Range("E8").FormulaR1C1 = "=Projections!R[-4]C[3]"
    Range("E9").FormulaR1C1 = "=Projections!R[-5]C[4]"
    Range("E10").FormulaR1C1 = "=Projections!R[-6]C[5]"
    Range("E11").FormulaR1C1 = "=Projections!R[-7]C[6]"
    Range("E12").FormulaR1C1 = "=Projections!R[-8]C[7]"
    Range("E13").FormulaR1C1 = "=Projections!R[-9]C[8]"

There is 11 input values taken from a row as you could see. When I change i I would like to change where I get the input to the row underneath (i.e row four instead of three when i=1). That's why I tried some ...Projections!R[-4]C[3]" to ...Projections!R[-4+i]C[3]" but as you told me, I have big problems with the syntax and the functions, I am a totally newbie in VBA.

This is not enough however. The for loop (i) also change where I paste the output from the sheet. The output is taken from the same place as before but pasted at another location.

Range((2), (i + 3)).Select    ' This is wrong
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                               SkipBlanks:=False, Transpose:=False

The first time its at C2, but the next time (i=1) I would like to post it at column D.

This things that I do would perfectly fine with just one loop if it begins in the beginning of the macro.

Most of the code is taken by the record macro function. The problems start when I have to change it in order to insert a working for loop.

Best Regars

E