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

  • Thread starter Thread starter Revolvr
  • Start date Start date
R

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
 
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
 
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.
 
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
 
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
 
Back
Top