Search code examples
excelvbarangecell

How to assign a value to a Range created with Cells() in VBA

I want to assign a value to multiple cells using a Range. I must define the range using variables, that's why I use the Cells() parameters, but when I create a Range object with Cells, the compilator returns a 1004 error (Application-defined or object-defined error). The error also occurs when not using variables, for example with Range(Cells(0,0), Cells(1,0)).

Here is my code

Dim Offset1 As Integer: Offset1 = 0
Dim Offset2 As Integer: Offset2 = 1
With ThisWorkbook.Worksheets("Sheet1")
  .Range(.Cells(Offset1, 0), .Cells(Offset2, 0)).Value = "NewValue"
End With
Solution

  • There are a few questions around your attempt but let me spell it out for you.

    If you want to use the .Range() approach then it requires a qualified range of cells as the parameter.

    This is what is expected (obviously within a With statement) ...

    .Range("A1:B10").Value = "NewValue"

    ... and if you want something like that but want it to be somewhat dynamic, then this as specified will produce the same result. You need to use .Address along with a proper row/column cell reference.

    .Range(.Cells(1, 1).Address & ":" & .Cells(10, 2).Address).Value = "NewValue"

    .Cells(1, 1) = Cell A1

    .Cells(10, 2) = Cell B10

    If you want to offset a specific cell, then you need a proper cell reference to begin with. Example, if you want 2 columns to the right and 5 rows down from cell B10, you would do this ...

    .Cells(10, 2).Offset(5, 2).Address

    You should be able to adapt that to your code.