Copying data down the column til then "nth" row

nirvehex

Active Member
Joined
Jul 27, 2011
Messages
486
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:
Code:
=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.
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

mrmmickle1

Well-known Member
Joined
May 11, 2012
Messages
2,456
Maybe something like this:

Code:
[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

Active Member
Joined
Jul 27, 2011
Messages
486
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

Active Member
Joined
Jul 27, 2011
Messages
486
Here's what I have so far and I'm getting an error on my LR = .formula

Code:
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

Well-known Member
Joined
May 11, 2012
Messages
2,456

ADVERTISEMENT

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

Code:
[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:

Code:
LR = ActiveSheet.ListObjects("Table2").ListRows.Count - 1
 
Last edited:

nirvehex

Active Member
Joined
Jul 27, 2011
Messages
486
Thanks! This almost works!

My code is

Code:
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

Well-known Member
Joined
May 11, 2012
Messages
2,456
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?
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,924
Messages
5,525,656
Members
409,658
Latest member
Yardcell

This Week's Hot Topics

Top