How can I set a range of cells using Cells with Range?

  • I would like to accomplish this:

    #1) Set DataColumn = Range("D24:D41")


    with something like this:

    #2) Set DataColumn = Range(Cells(R0 + 1), Cells(R0 + Fixedn))


    #1 works, #2 does not work.


    where R0 and Fixedn contain integer values. I want to make this change is because the range of cells differs from time to time.


    I tried the following, but it doesn't work either.


    Code
    1. With Worksheets("samplingplan")
    2. Set DataColumn = .Range(.Cells(R0 + 1), .Cells(R0 + Fixedn))
    3. End With
    4. DataColumn.ClearContents


    How can I fix this?

  • HI Stan,


    the syntax for range using .cells is .range(.cells(row, column),.cells(row,column)) so to get D24:D41 using this syntax you need somthing like:


    Don't forget to wrap the code in your post, just highlight and click the code </> icon.


    Regards

    Justin

  • Thanks Justin, that is working, but I don't understand what With..End With does. For example, the following requires With..End Within order to work:

    Code
    1. With samplingplan
    2.       .Cells(R0 + Fixedn + 1, 5).Value = CumAverage
    3. End With

    But the following works without With..End With:

    Code
    1. Worksheets("samplingplan").Cells(11, 12) = MeanNow

    What explains this, and what factors should I look for to recognize when I should use each method?

  • The with just allows a bit more flexibility and can save time if you add to the code later, the with can incorporate a number manipulations to the chosen object, range etc... without having to type that text each time similar to the use of declaring ws as a worksheet then setting it to a specific worksheet can allow you to modify a single line of code but adapts the requirement throughout the sub... it also allows you to move the code easily between forms and sheets often by changing only a couple of pieces of the code rather than looking through and finding every reference to the objects and ranges you need to change. As you note though, if you are only ever going to perform a single manipulation it probably simplifies the code to reference the object directly, if you intend to get more involved in VBA programming then making your code as adaptable as possible from the start is a good idea and you learn a great deal as you crash into the difficulties that arise in declaring and setting objects using variables on sheets, forms or within the code.


    That is my understanding, there may be more to it if one of the programmers wants to jump in, glad my earlier response helped to fix your current issue.