VBA to reference one cell in a range of one or more cells

[Revolvr]

Hi all,

I have some VBA code that needs to manipulate data in a named range. In the
code I use this:

mydata = Range("MyData")

This is normally a range of cells, so "mydata" becomes an array, so I can do
something like:

a = mydata(ir, ic)

Where ir and ic are integers representing the row and column in the range.
(Note that there is no DIM statement for mydata).

However, if per chance the range MyData contains only 1 cell I get a "Type
Mismatch" error on the above code because the variant mydata is no longer an
array, but a single parameter.

So this means I have to put a check in my code like this:

if ( Range("MyData").count = 1 ) then
a = mydata
else
a = mydata(ir,ic)
end if

I would rather not do this "IF" test each time. Are there some better ways
to reference values in the MyData range?

Thanks,

-- Rev

 

[Bernie Deitrick]

Rev,

No need for the check:

Dim mydata As Range
Set mydata = Range("MyData")
a = mydata.Cells(ir, ic).Value

This will work even when ir and ic are negative, zero, or greater than the number of rows and
columns in MyData, as long as the relative addressing doesn't extend beyond the bounds of the
worksheet. For example:

Dim mydata As Range
Dim iR As Long
Dim iC As Integer

Set mydata = Range("C5")
For iR = -(mydata.Row - 2) To 3
For iC = -(mydata.Column - 2) To 3
MsgBox "Row " & iR & ", Column " & iC & " of myData is cell " _
& mydata.Cells(iR, iC).Address
Next iC
Next iR


HTH,
Bernie
MS Excel MVP

 

[Revolvr]

Thanks - that's what I needed.

I noticed that

a = mydata.Cells(ir, ic).Value

Works but

a = mydata(ir, ic)

seems to work as well.

 

[Bernie Deitrick]

Rev,

Yes, as long as you dimension mydata as a range, those are equivalent.
Cells is the default property of a range object, which the indexing allows
you to access using either of these methods

mydata(ir,ic)
mydata.Cells(ir,ic)

That is why you can use this:

For Each Cell In Range("Whatever")

instead of having to be more specific:

For Each Cell In Range("Whatever").Cells

Bernie

 

[Bernie Deitrick]

I should have also noted that Value is the default property of a single cell
range object, which is why

a = mydata(ir, ic)

Fails when mydata is a single cell range - it is no longer accessing the
Cells property with the (ir,ic) indices.

Bernie