How can I reference a cell in a named range with an offset?-CodePudding

I'm trying to work out how in VBA to read and update a particular row in one range, based upon the row I'm looking at in another range the same size.

So let's say A3:A9 is one range "Alpha", and F18:F24 is the other range "Foxtrot".

I know that A5 has been clicked and since querying the Row property of the "Alpha" returns the starting row for the range, I can determine the location of the cell clicked within Alpha with the code:

iRowOffset = cell.Row - Range("Alpha").Row

I've figured out how to read the same row in Foxtrot

Range("Foxtrot").Value2(iRowOffset   1, 1)

What I can't figure out is how to actually update Foxtrot

Range("Foxtrot").Value2(iRowOffset   1, 1) = 3

doesn't error, but also doesn't update the value in the spreadsheet, or in the Range object when viewed through Locals.

What I'm trying to avoid is hardcoding the relative positions of the two ranges, e.g. by offsetting from the cell in Range("Alpha") since I'd like the flexibility to move Foxtrot around on the sheet without having to worry about updating the code.

CodePudding user response：

Range("Foxtrot").Value2(iRowOffset   1, 1)

indexes into the array returned by .Value2.

You need to actually refer to a Range object.

Range("Foxtrot").Cells(iRowOffset   1, 1).Value2 = 3