Simple Loop - To save me typing 50 times!

[BenjaminDubai]

What I would like to do is repeat the below formula changing 2 things 50 times.

What I want to change is highlighted.

'Add formaula to cell

Sheets("Resources").Select
Range("A3").Select
ActiveCell.FormulaR1C1 = _
"=AND('Resource%PerWeek'!RC2=Resources!R1C,Dates!RC1=TRUE)"

'Autofill the page
Application.Run "'Resource%Calculator.xls'!AutofillArea"

'Select the range that is now correct, copy and paste values

Sheets("Resource%PerWeek").Select
Range("C3:C1119").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False


I would like the column to move from 1 to 2, to 3, etc... until 50.

I would like the range to move from C3:C1119 to D3:D1119 to E3:E1119 etc until it hits AZ3:AZ1119

Can you help?

 

[jindon]

Not sure, but something like this?

    With Sheets("Resource%PerWeek").Range("C3:BA1119")
         .Value = .Value
    End With

 

[BenjaminDubai]

Thank you -- but this turns my cells into values not formulas - it does it in a nicer way that I knew - great.

But this does not help me with what I need.

I need to add a formula

ActiveCell.FormulaR1C1 = _
"=AND('Resource%PerWeek'!RC2=Resources!R1C,Dates!RC1=TRUE)"

to a region. Once this is in place then the first column ("C:C") of another workbook contains the correct figures. Hence then I want to .Value them.

After I have done that I want to change formula in the area to be based on a new date. The next date is in Dates!R1C2. Hence:


ActiveCell.FormulaR1C1 = _
"=AND('Resource%PerWeek'!RC2=Resources!R1C,Dates!RC2=TRUE)"

then i autofill the region. Once it is autofilled then column 2 ("D:D") of my workbook contains the figures that I want. So I .Value them.

I want to do this 50 times. Can I do something like

Let x = 1 to 50 and y = C to AZ

and put in X's and y's so that this code repeats ??