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

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


"Revolvr" wrote in message
...
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" <deitbe @ consumer dot org wrote in message
...
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" wrote in message
...
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