Copying data down the column til then "nth" row

nirvehex

Active Member
Joined
Jul 27, 2011
Messages
493
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

mrmmickle1

Well-known Member
Joined
May 11, 2012
Messages
2,461
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
493
Office Version
  1. 365
Platform
  1. Windows
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
493
Office Version
  1. 365
Platform
  1. Windows
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,461

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
493
Office Version
  1. 365
Platform
  1. Windows
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,461
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:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,487
Messages
5,831,975
Members
430,099
Latest member
rdhoy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top