need help passing parameters to OFFSET
GET_WEIGHT = Activesheet.Evaluate("=OFFSET(CRITERIA_WEIGHT_TABL E_2," & r1 &
", " & c1 & ", 1, 1)")
You are mixing up the formula text and the variables, you have to
concatenate them in a string to pass to Evaluate.
--
---
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my addy)
"mike" wrote in message
...
Excel 2003 (11.8211.8202) SP3
Microsoft Windows XP Professional Service pack 2
New to Excell programming.
I wrote a function that allows me to pass in a row and column to be
used to access a table.
When I run the code the two variables 'r' & 'c' have the correct
values r1 = 1 and c1 = 1 but the OFFSET operation always evaluates to
r1= 0 and c1 = 0.
I even tried hardcoding r1 and c1 but they seem to evaluate to 0,0
even though the debugger indicates they are have the correct values
r1 = 1 and c1 = 1...
I am thinking that the syntax is wrong for passing in the parameters
for row and column into the OFFSET function,
Any help would be greatly appreciated. - mike
Function GET_WEIGHT(row, column)
Dim r1 As Long 'Variant
Dim c1 As Long
r1 = 1 'hardcoded
c1 = 1 'hardcoded
GET_WEIGHT = [=OFFSET(CRITERIA_WEIGHT_TABLE_2, 2, 2, 1, 1)] <- This
works fine
GET_WEIGHT = [=OFFSET(CRITERIA_WEIGHT_TABLE_2, r1, c1, 1, 1)] <- This
allways makes r1 and c1 = 0 when they really = 1
End Function
|