nirvehex

09-24-2014, 06:51 AM

Hi,

I have data that is pulling from a table, Table2. In column A on a separate work sheet called "Client", starting in row 2, the formula is:

=OFFSET(Table2[@LocationCode],-1,0,2,1)

Is there a way I can make this auto populate down column A to the "nth" row so that it is the same amount of rows as the table is?

Otherwise I have to go count how many rows are in the table and then copy the formula down that many rows on the client worksheet.

Here's my code so far that is not quite working:

Sub PreBidClient()

Sheets("Client Pre-Bid").Select

Dim LR As Long

LR = .Formula = "=COUNTA(Table2[[#All],[LocationCode]])-1"

Range("A2:A" & LR).Formula = "=OFFSET(Table2[@LocationCode],-1,0,2,1)"

End Sub

Any ideas?

Thank you!

I have data that is pulling from a table, Table2. In column A on a separate work sheet called "Client", starting in row 2, the formula is:

=OFFSET(Table2[@LocationCode],-1,0,2,1)

Is there a way I can make this auto populate down column A to the "nth" row so that it is the same amount of rows as the table is?

Otherwise I have to go count how many rows are in the table and then copy the formula down that many rows on the client worksheet.

Here's my code so far that is not quite working:

Sub PreBidClient()

Sheets("Client Pre-Bid").Select

Dim LR As Long

LR = .Formula = "=COUNTA(Table2[[#All],[LocationCode]])-1"

Range("A2:A" & LR).Formula = "=OFFSET(Table2[@LocationCode],-1,0,2,1)"

End Sub

Any ideas?

Thank you!