[FIXED] How can I make a cell reference a function of a variable?

Issue

I will be using the same macro 24 times, but the ranges will differ (but at a constant rate) every time. Therefore I thought it would be smart to write the references as a function in order to reduce the length of the code, and also make it easier to change.

Now it looks like this:

    Sheets("Planned time").Select
    Range("I15:NJ32").Select
    Selection.Copy
    Sheets("Diff").Select
    Range("I9").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

But I would like it to be something like this:

    Sheets("Planned time").Select
    Range("I(15+171x):NJ(32+171x)").Select
    Selection.Copy
    Sheets("Diff").Select
    Range("I(9+56x)").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Where the macro loops 24 times, but for each loop, increase the value of x by 1. x would need to be 0 in the first iteration, and 23 in the last one (totalling 24 iterations).

I know the coding is probably far off, but I think it illustrates what I want to accomplish in a good way.

Any assistance would be greatly appreciated.

UPDATE: Solved

Dim x As Integer

For x = 0 To 23

Sheets("Planned time").Select

Range("I" & cstr(15+171*x) & ":NJ" & cstr(32+171*x)).Select

Selection.Copy

Sheets("Diff").Select

Range("I" & cstr(9+56*x)).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Next x

Solution

You want to build a string expression that, when evaluated in VBA, will be a valid address:

Range("I" & cstr(15+171*x) & ":NJ" & cstr(32+171*x)).Select

I assume you know how to build a FOR loop.

You can also 'build' range references by using numeric cell(row, column) expressions:

Range(cell(15+171*x,10),cell(32+171*x,374)).select 

Answered By - tinazmu

Answer Checked By - Mary Flores (PHPFixing Volunteer)