need help passing parameters to OFFSET
 

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

need help passing parameters to OFFSET
 

I'm no expert, but I'll give you some random thoughts to consider until one
of the gurus gets a chance to respond.

The offset formula is looking for a discrete number of rows and columns;
while you set the value to 1, I can't help but wonder if there is a type
mismatch going on between your Long and the offset formula. Try dimming both
your variables as an integer and see if that makes any difference. If Excel
is storing your value of 1 as .99999999998 or 1.0000000000000003 then the
offset wouldn't know what to do with it.

If you don't get a working solution, post a larger section of your code (any
loops,
etc.)- unless this problem is replicated with just the code you posted. It
might
help if you also post the definition of CRITERIA_WEIGHT_TABLE_2, just for
clarity. Is your intent to have the function return a range (the offset
range) or the value of some part of that range? You may also find value
looking at Excel.range.offset as another way to calculate your offset in VBA

Good luck,
Keith

"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

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

need help passing parameters to OFFSET
 

Keith,
Thanks for taking the to respond.
The person that responded after you found my problem.
Thanks again I appreciate it. - mike

On Mar 25, 5:46*pm, "Ker_01" wrote:
I'm no expert, but I'll give you some random thoughts to consider until one
of the gurus gets a chance to respond.

The offset formula is looking for a discrete number of rows and columns;
while you set the value to 1, I can't help but wonder if there is a type
mismatch going on between your Long and the offset formula. Try dimming both
your variables as an integer and see if that makes any difference. If Excel
is storing your value of 1 as .99999999998 or 1.0000000000000003 then the
offset wouldn't know what to do with it.

If you don't get a working solution, post a larger section of your code (any
loops,
etc.)- unless this problem is replicated with just the code you posted. It
might
help if you also post the definition of CRITERIA_WEIGHT_TABLE_2, just for
clarity. Is your intent to have the function return a range (the offset
range) or the value of some part of that range? You may also find value
looking at Excel.range.offset as another way to calculate your offset in VBA

Good luck,
Keith

"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- Hide quoted text -

- Show quoted text -

need help passing parameters to OFFSET
 

Bob,
I owe you big time! Thanks that was it.
I was hacking at this on the side for a few days, it was driving me
nuts!
I really appreciate the help!
Thank you! Thank you! Thank you! - mike



On Mar 25, 6:39*pm, "Bob Phillips" wrote:
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- Hide quoted text -

- Show quoted text -