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
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
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.
Answered By – Skin
Answer Checked By – David Goodson (AngularFixing Volunteer)