Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to return the reference of a range of cells | Excel Discussion (Misc queries) | |||
How do I reference a range of cells depending on another cell valu | Excel Discussion (Misc queries) | |||
Formulas that reference cells that reference another cell | Excel Discussion (Misc queries) | |||
reference format Range(Cells(),Cells()) | Excel Programming | |||
How do I reference the same cell as I move through range of cells. | Excel Worksheet Functions |