Using for loops when referencing

**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

**shg** · 08-20-2009, 03:45 PM

How about posting a workbook and explaining in context?

**Operator** · 08-21-2009, 12:03 PM

I resolved one part of it. The upper part.

This i did with this phrase

ActiveCell.Value = Worksheets("Project Demand").Cells(4 + i, "b").Value

However, I did not solve the last part.

  
    Range("M17:M340").Select
   Selection.Copy
    Sheets("Part Demand").Select
    Cells(3, 3 + i).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

The upper part works fine but the code beneath gets a run-time error 1004. Paste SPecial method of range class failed.

   Sheets("Needed Weekly").Select
    Range("I17:J340").Select
    Selection.Copy
    Sheets("Part Pivot").Select
    Cells(2 + i * 324, "a").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Do you have any suggestions? (Some sheets have changed their names, I am aware of that).

I am sorry for not being able to post the sheet here.

**Operator** · 08-21-2009, 02:05 PM

It's solved Part Pivot was referring to a pivot table. It was suppose to refer to the pivot raw data.

Dim i


For i = 0 To 51 Step 1

    'select which week's forecast to calculate to parts and calculates it
    Sheets("Needed Weekly").Select
    Range("E2").Select
    ActiveCell.Value = Worksheets("Project Demand").Cells(4 + i, "b").Value
    Range("E3").Select
    ActiveCell.Value = Worksheets("Project Demand").Cells(4 + i, "c").Value
    Range("E4").Select
    ActiveCell.Value = Worksheets("Project Demand").Cells(4 + i, "d").Value
    Range("E5").Select
    ActiveCell.Value = Worksheets("Project Demand").Cells(4 + i, "e").Value
    Range("E6").Select
    ActiveCell.Value = Worksheets("Project Demand").Cells(4 + i, "f").Value
    Range("E7").Select
    ActiveCell.Value = Worksheets("Project Demand").Cells(4 + i, "g").Value
    Range("E8").Select
    ActiveCell.Value = Worksheets("Project Demand").Cells(4 + i, "h").Value
    Range("E9").Select
    ActiveCell.Value = Worksheets("Project Demand").Cells(4 + i, "i").Value
    Range("E10").Select
    ActiveCell.Value = Worksheets("Project Demand").Cells(4 + i, "j").Value
    Range("E11").Select
    ActiveCell.Value = Worksheets("Project Demand").Cells(4 + i, "k").Value
    Range("E12").Select
    ActiveCell.Value = Worksheets("Project Demand").Cells(4 + i, "l").Value
    Range("E13").Select
    ActiveCell.Value = Worksheets("Project Demand").Cells(4 + i, "m").Value
  
    

    ' Part Demand (inactual)
     'copies the amount needed to part demand
    'Range("M17:M340").Select
   ' Selection.Copy
    'Sheets("Part Demand").Select
   ' Cells(3, 3 + i).Select
    'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    ':=False, Transpose:=False
    
    ' Part Pivot
    ' copies the part number and description
    
   Sheets("Needed Weekly").Select
    Range("I17:J340").Select
    Selection.Copy
    Sheets("Pivot Source").Select
    Cells(2 + i * 324, "a").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    ' copies the amount needed and the vc
    
    Sheets("Needed Weekly").Select
   Range("M17:N340").Select
    
    Selection.Copy
    Sheets("Pivot Source").Select
    Sheets("Pivot Source").Parent.Activate ''
    Cells(2 + i * 324, "c").Select
    
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    
    ' adds the right week
    Sheets("Pivot Source").Select
    Range(Cells(2 + i * 324, "e"), Cells(325 + i * 324, "e")) = i + 1


    Next i