need help passing parameters to OFFSET

[mike]

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

 

[Ker_01]

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

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

 

[Bob Phillips]

GET_WEIGHT = Activesheet.Evaluate("=OFFSET(CRITERIA_WEIGHT_TABLE_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)

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

 

[mike]

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

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

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 -

 

[mike]

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

GET_WEIGHT = Activesheet.Evaluate("=OFFSET(CRITERIA_WEIGHT_TABLE_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)

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 -