Search code examples
excelrangeoffsetnamed-rangesvba

VBA Excel. Edit a range based on an offset from a named cell

I don't know if this is possible, but it feels like it should be.

I have a cell with a defined name: "Data_A" (It is a single cell, ie a 1x1 range).

I want to write an array (N x M) to the cells near it (ie, i columns across, and j rows down).

I know how to write an array to a named range if they are the same dimensions, and I know how to offset the range.

In that case I would do the following:

'"Data_A" refers to a named range of cells that is 2x3 in size.
'"wks_datainput" is the worksheet that Data_A sits in.
Dim Outputs() as Double: Redim Outputs(1 to 2, 1 to 3)
wks_datainput.Range("Data_A").Offset(6,7).value = Outputs

My Question is: What if "Data_A" refers to a named range that is 1x1? How do I write to the cells nearby if the dimensions of "Data_A" do not match the dimensions of "Outputs"?

Secondary question - How do I clear those cells (without using a loop)?

 wks_datainput.Range("Data_A").Offset(6,7).ClearContents
Solution

  • I think this then. Resize is very useful.

    Dim Outputs() As Double: ReDim Outputs(2, 3)
Range("Data_A").Offset(6, 7).Resize(UBound(Outputs, 1), UBound(Outputs, 2)).Value = Outputs