Just can't get my head around loops.. (easy answer I'm sure)

BenjaminDubai

New Member
Joined
May 7, 2007
Messages
42
I want to run this maco (pasting formulas into cells, then copying and pasting the values)


Range("N3").Select
ActiveCell.FormulaR1C1 = "=AND(RC[-10]=R3C1,RC[-1]=TRUE)"
Range("N3").Select
Selection.AutoFill Destination:=Range("N3:N1119")

Range("O3").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=TRUE,1/R2C13,"""")"
Range("O3").Select
Selection.AutoFill Destination:=Range("O3:O1137")

Range("O3:O1137").Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
True, Transpose:=False

I next want to then use the same macro again but in this top third I want to paste base my calculations on the next cell down

Range("N3").Select
ActiveCell.FormulaR1C1 = "=AND(RC[-10]=R3C2,RC[-1]=TRUE)"
Range("N3").Select
Selection.AutoFill Destination:=Range("N3:N1119")

Range("O3").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=TRUE,1/R2C13,"""")"
Range("O3").Select
Selection.AutoFill Destination:=Range("O3:O1137")

Range("O3:O1137").Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
True, Transpose:=False

the rest is exactly the same. Now the only way I can do it is re-copy and re-paste the marco 115 times.

Can I run a loop that puts an x in instead of the number that I want fo change and runs it from 1 - 115 ???
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
Benjamin

What are you actually trying to achieve?

Why are you entering formulas, then replace them with different formulas 115 times?
 

BenjaminDubai

New Member
Joined
May 7, 2007
Messages
42
OK

I have a list of tasks each with resources assigned to them: (let / = cell break)


1 / R1
1 / R2
1 / R3
2 / R1
2 / R2
2 / R3
2 / R4
2 / R5
2 / R6
2 / R7
2 / R8
3 / R1
4 / None
5 / R3
5 / R4
5 / R1

etc different resources to each task. In total I have 46 resources spread across 662 tasks.

Now for each of the 50 weeks in my project I want to assign a percentage of weekly time for the resource to work on the project. Obviously each resource has 100 % per week.

Lets use week 1 as an example:

Week1

1 / R1 / 25%
1 / R2 / 50%
1 / R3 / 33%
2 / R1 / 25%
2 / R2 / 50%
2 / R3 / 33%
2 / R4 / 50%
2 / R5 / 100%
2 / R6 / 100%
2 / R7 / 100%
2 / R8 / 100%
3 / R1 / 25%
4 / None this week
5 / R3 / 33%
5 / R4 / 50%
5 / R1 / 25%
6 / None this week

Now ideally I would like a formula in each cell that says if the task is active during this week - then count all of the active tasks for which I (the resource mentioned in the row) am active and add that figure e.g. 4 (for R1). Then the cell value is 1/4 - or 25%.

If you can figure out from that explanation how to do it - GREAT. But I would like to know how to do a loop anyway. Did my previous explantion make sense? I just want to change the cell that the formula is inserted into 115 times....
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
Benjamin

You just lost me with that last part.

You seemed to be indicating that you wanted to use the loop to change the formula, not the cell the formula is inserted into.
ActiveCell.FormulaR1C1 = "=AND(RC[-10]=R3C2,RC[-1]=TRUE)"
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
Well if that's the case then it brings me back to my original point.

Why do you want to paste a slightly different formula 115 times into the same range?

Am I missing something?
 

Forum statistics

Threads
1,181,410
Messages
5,929,774
Members
436,688
Latest member
sunnyBNH013

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top