Struggling

[Goofy]

Hi

Im fairly new to programming in Excel, but I am a .NET programmer so Im not
new to coding. I have some basic questions on approach to some fundemental
things.

I have a NamedRange which is in effect a multi dimentional array

Week StartColumn EndColumn Row
1 4 19 5
2 4 19 57

. . . . . ETC

What is the best way of returning the value of any cell given the WeekNumber
and Column Number

Thanks

 

[Goofy]

Dont worry, I found it.

Range.Cells(Row,Col)

Thanks Anyway

 

[Bob Phillips]

Function GetValue(WeekNum, ColNo)

GetValue = Application.Index(Range("A"), _
Application.Match(WeekNum, Range("A:A"), 0), ColNo)

End Function


mVal = Getvalue(WeekNum:=2, ColNo:=3)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[John Coleman]

I'm glad that you figured it out. If you are looping through large
ranges with an explicit Range("foo").Cells(i,j) you might find that
performance is too slow. A few things can be done to help:
1) Use the With .. End With construct:
With Range("foo")
For i = 1 to m
For j = 1 to n
process .Cell(i,j)
Next j
Next i
End With

2) Create a range variable whose name is the same as the named range:
Dim foo As Range
Set foo = Range("foo")

The Range("foo").Cells(i,j) can be replaced by foo.Cells(i,j).
I'm not sure how much it helps speed-wise (though I think it does), but
in any event it makes the code more readable.

3) Assign the Range directly into a Variant holding an array:

Dim foo As Variant '(Dim foo() As Variant would also work)
foo = Range("foo")
Then Range("foo").Cells(i,j) can be replaced by a simple foo(i,j)

This works somewhat counter-intuitively if "foo" names a 1-dimensional
range of cells. If "foo" refers to A1:A5 for example and you ran foo =
Range("foo") you might expect that foo(3) would yield the value in A3.
Instead, it throws a run-time "subscript out of range" error. You would
need to write foo(3,1) to get at A3. Just remember that VBA treats all
ranges as rectangular and that you need to (in this case) explicitly
supply both a row and a column number and you will be ok.

Hope that helps

-John Coleman