Inserting Offset equation using VBA

**agentred** · 05-19-2010, 06:21 PM

Hi Folks,

I'm having trouble and wondered if anyone could help:

I have two worksheets. The first sheet contains a column of 10 values. I have named the cell in the last row "final"

I want to put these values in to the second sheet using the offset function. So the first cell in the second sheet would have the equation

=OFFSET(final,-9,0)

The second cell would have the equation

=OFFSET(final, -8, 0)

The third would be

=OFFSET(final, -7, 0)

and so on down to the last row which would be

=OFFSET(final, 0, 0)

and sheet one and sheet two would be identical. How would I accomplish this? I don't want to use the offset vba function because I want the formula inside the cells. The trouble I'm having is with the row offset number changing, I don't know how to code that.

Any ideas would be appreciated, many thanks for your help,

AR

**shg** · 05-19-2010, 06:32 PM

In the first cell and copy down, =INDEX(Sheet1!A:A, ROW(final) + ROW() - 10)

... or

=INDEX(Sheet1!$1:$65536, ROW(final) + ROW() - 10, COLUMNS(final))

**Richard Buttrey** · 05-19-2010, 06:32 PM

Hi,

Something like:

Sub OffsetTen()
    Dim l As Long

    For l = 10 To 1 Step -1
        Sheet2.Range("A" & l) = "=Offset(final," & -l & ",0)"
    Next l
End Sub

perhaps?

HTH

**shg** · 05-19-2010, 06:42 PM

Sorry, you did say VBA. Another way:

Sub x()
    Sheet2.Range("A1:A10").Formula = "=INDEX(Sheet1!$1:$65536, ROW(final) + ROW() - 10, COLUMNS(final))"
End Sub

**agentred** · 05-19-2010, 06:46 PM

Originally Posted by Richard Buttrey

Hi,

Something like:

Sub OffsetTen()
    Dim l As Long

    For l = 10 To 1 Step -1
        Sheet2.Range("A" & l) = "=Offset(final," & -l & ",0)"
    Next l
End Sub

perhaps?

HTH

But... I could've sworn I tried the exact same formula and it didn't work, but then I tried copying and pasting your code and it works almost perfectly!

I say almost because there's one hitch... Because the range is the same as the offset, the column ends up being inverted? Like it starts at 10 and ends at 1, instead of the other way around. So I edited it to this

    Dim l As Long
    Dim j As Integer
    
    For l = 9 To 1 Step -1
    j = 9 - l
        Range("A" & j).Value = "=Offset(open," & -l & ",0)"
    Next l

And it's perfect.

Thanks for your help!!

**Richard Buttrey** · 05-20-2010, 09:19 AM

Hi,

In that case why not just use a loop counter from 1 to l, rather than a reverse (step -1) loop counter and then having to invert that with a j=9-l

Rgds