Copying data down the column til then "nth" row

[nirvehex]

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.

Any ideas?

Thanks.

 

[mrmmickle1]

Maybe something like this:

[COLOR=#0000ff] Dim [/COLOR]lr [COLOR=#0000ff]As Long[/COLOR]
 lr = [COLOR=#ff0000]YourNumericValueHere [/COLOR][COLOR=#008000]'Ex.  1052[/COLOR]

 Range("B2:B" & lr).Formula = "=OFFSET(Table2[@LocationCode],-1,0,2,1)"

 

[nirvehex]

What do you mean by "YourNumbericValueHere" ? I'm trying to get it to copy the formulas down the column as long as Table2 goes down the column.

 

[nirvehex]

Here's what I have so far and I'm getting an error on my LR = .formula

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!

 

[mrmmickle1]

I don't work with tables to much. Maybe you could use COUNTA with syntax like this:

[COLOR=#0000ff]Sub[/COLOR] PreBidClient()

[COLOR=#0000ff]     Dim[/COLOR] LR[COLOR=#0000ff] As Long

     [/COLOR][COLOR=#333333]Sheets("Client Pre-Bid").Select[/COLOR]
     LR = Application.WorksheetFunction.CountA(Columns(1)) - 1
     Range("A2:A" & LR).Formula = "=OFFSET(Table2[@LocationCode],-1,0,2,1)"

[COLOR=#0000ff]End Sub[/COLOR]

You could also use this:

LR = ActiveSheet.ListObjects("Table2").ListRows.Count - 1

 

[nirvehex]

Thanks! This almost works!

My code is

Sub PreBidClient()
Sheets("Client Pre-Bid").Select
Dim LR As Long
LR = Worksheets("Services Export").ListObjects("Table2").ListRows.Count - 1
Range("A2:A" & LR).Formula = "=OFFSET(Table2[@LocationCode],-1,0,2,1)"
End Sub

However, it seems to copy past the end of Table2 because I get a bunch of 0's all the way down to row 1,048,574. Table2 is only a couple hundred rows long.

My formual should only copy the formula down to LR, but it keeps going way past LR. So I'm not sure if the LR = part is bad or if the Range("A2:A" & LR).Formula part is bad.

Any thoughts?

 

[mrmmickle1]

Try stepping through the code using F8. Ask yourself the following questions. They may help you to solve the issue.

1. What is the value of LR after you execute the LR =... line of code?
2. Are you referencing the correct sheets and tables?
3. Have you formatted the entire spreadsheet like a table? This will drag the formula down to the end of the table (Not the last occupied row of the table).....
4. Formulas should autofill in a table without needing VBA
5. If you make a table dynamically using VBA you may have already defined the last row someplace in the code. Just use this variable in place of LR
6. What range does your table occupy?