Obvious, but couldn't find this either
 

I tried to use the OFFSET function within my VBA code, but the compiler
doesn't recognize it (which is wierd, because when I make a function, it is
visible in vba as a function for a cell). I'm sure there's a way to do it,
I'm just missing it. Thanks!

Obvious, but couldn't find this either
 

Did you inform Excel what the offset is in relation to?

Range("A1").Offset(1,2).Value

ActiveCell.Offset(3,1).Select

--
Kevin Backmann


"Gary F" wrote:

I tried to use the OFFSET function within my VBA code, but the compiler
doesn't recognize it (which is wierd, because when I make a function, it is
visible in vba as a function for a cell). I'm sure there's a way to do it,
I'm just missing it. Thanks!

Obvious, but couldn't find this either
 

I was actually trying to use the Offset function, not the range offset
method.... but it now occurs to me that you use excel functions in
cells/ranges. e.g., you click on A1 and type "=OFFSET(...)", so I'm sure
that what I have to do is something like

1. instantiate a cell (range)
2. set #2s value to "=OFFSET([parm1],...[parm4]")
3. catch the result

Right? The offset function I"m referring to is the one you get when you're
in a spreadsheet and you look at the list of available functions. I could be
just as well referring to any of the functions "ABS, VLOOKUP", etc.

Thanks

"Kevin B" wrote:


Did you inform Excel what the offset is in relation to?

Range("A1").Offset(1,2).Value

ActiveCell.Offset(3,1).Select

--
Kevin Backmann


"Gary F" wrote:

I tried to use the OFFSET function within my VBA code, but the compiler
doesn't recognize it (which is wierd, because when I make a function, it is
visible in vba as a function for a cell). I'm sure there's a way to do it,
I'm just missing it. Thanks!

Obvious, but couldn't find this either
 

Does this help?

Inserting a Worksheet Function into a Cell
To insert a worksheet function into a cell, you specify the function as the
value of the Formula property of the corresponding Range object. In the
following example, the RAND worksheet function (which generates a random
number) is assigned to the Formula property of range A1:B3 on Sheet1 in the
active workbook.

Sub InsertFormula()
Worksheets("Sheet1").Range("A1:B3").Formula = "=OFFSET()"
End Sub



"Gary F" wrote:

I was actually trying to use the Offset function, not the range offset
method.... but it now occurs to me that you use excel functions in
cells/ranges. e.g., you click on A1 and type "=OFFSET(...)", so I'm sure
that what I have to do is something like

1. instantiate a cell (range)
2. set #2s value to "=OFFSET([parm1],...[parm4]")
3. catch the result

Right? The offset function I"m referring to is the one you get when you're
in a spreadsheet and you look at the list of available functions. I could be
just as well referring to any of the functions "ABS, VLOOKUP", etc.

Thanks

"Kevin B" wrote:


Did you inform Excel what the offset is in relation to?

Range("A1").Offset(1,2).Value

ActiveCell.Offset(3,1).Select

--
Kevin Backmann


"Gary F" wrote:

I tried to use the OFFSET function within my VBA code, but the compiler
doesn't recognize it (which is wierd, because when I make a function, it is
visible in vba as a function for a cell). I'm sure there's a way to do it,
I'm just missing it. Thanks!

Obvious, but couldn't find this either
 

Why wouldn't you use the offset property of the range object in VBA? it is
faster, readily available and would accomplish the same thing.

=Offset(A1,3,5)

range("A1").Offset(3,5).Value

3 steps for the first approach (as you have laid them out), 1 step for the
second.

--
Regards,
Tom Ogilvy


"Gary F" wrote:

I was actually trying to use the Offset function, not the range offset
method.... but it now occurs to me that you use excel functions in
cells/ranges. e.g., you click on A1 and type "=OFFSET(...)", so I'm sure
that what I have to do is something like

1. instantiate a cell (range)
2. set #2s value to "=OFFSET([parm1],...[parm4]")
3. catch the result

Right? The offset function I"m referring to is the one you get when you're
in a spreadsheet and you look at the list of available functions. I could be
just as well referring to any of the functions "ABS, VLOOKUP", etc.

Thanks

"Kevin B" wrote:


Did you inform Excel what the offset is in relation to?

Range("A1").Offset(1,2).Value

ActiveCell.Offset(3,1).Select

--
Kevin Backmann


"Gary F" wrote:

I tried to use the OFFSET function within my VBA code, but the compiler
doesn't recognize it (which is wierd, because when I make a function, it is
visible in vba as a function for a cell). I'm sure there's a way to do it,
I'm just missing it. Thanks!