Selection.End(xldown).Select variable number of times

[Will85]

In column A, I have strategically placed the value of x in specific rows.

I use a macro that goes to column A and then executes selection.end(xldown).Select a certain number of times depending on which row I want it to land on.

For example if I want it to select the third row with an x I use:

Range("A1").Select
selection.end(xldown).Select
selection.end(xldown).Select
selection.end(xldown).Select


I would prefer to be able to tell it to go down a specific # of times based on the value in a cell on another sheet.

Additionally, I need it to look up this value, instead of pointing to a specific cell reference.

For example, Id want to index match the value based on an account number, the account number its looking up can be hardcoded inside the macro itself.

Sheet2 has account numbers in column A and the value I am seeking in column B.

 

[Fluff]

Ok, glad we cleared up the error & apologies for the typo.

Can you please explain what your end goal is, trying to loop an xlDown is not a very good way of doing things & could lead to problems.

 

[Will85]

Ok, glad we cleared up the error & apologies for the typo.

Can you please explain what your end goal is, trying to loop an xlDown is not a very good way of doing things & could lead to problems.

Super general explanation would be that I am copying the desired row with the x and pasting it in the row above.

Because I am constantly adding and deleting rows, I never know where the row with the x is going to be.

The row being copied represents a unique account number. I have multiple rows with x's because I have multiple account numbers, and its a user decision to decide which account number they need to duplicate. My account numbers are in order, so I would know that if account number 3 is the one being replicated, i would xldown three times. However, when new account numbers are created, Account number 3 may have changed in rank, and so the number of xldown needs to be adjusted. Instead of adjusting my macro, Id rather have a table that tracks their position. Hence the attempt at incorporating the vlookup into my xldown loop.

 

[Fluff]

Other than the X in col A, is there any other way to determine what row needs to be copied?

 

[DanteAmor]

Super general explanation would be that I am copying the desired row with the x and pasting it in the row above.

Because I am constantly adding and deleting rows, I never know where the row with the x is going to be.

The row being copied represents a unique account number. I have multiple rows with x's because I have multiple account numbers, and its a user decision to decide which account number they need to duplicate. My account numbers are in order, so I would know that if account number 3 is the one being replicated, i would xldown three times. However, when new account numbers are created, Account number 3 may have changed in rank, and so the number of xldown needs to be adjusted. Instead of adjusting my macro, Id rather have a table that tracks their position. Hence the attempt at incorporating the vlookup into my xldown loop.

I'm also a bit lost, it would help if you share a file with us. In the file explain with steps the process and what you expect of result.


You could upload a copy of your file to a free site such www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[Will85]

Other than the X in col A, is there any other way to determine what row needs to be copied?

Not that I can think of, its completely user dependent on what account number they want to duplicate.

In the row below each x, I have a sub-total, next to the subtotal I have manually inserted a form control button (which activates the macro for that account).

My form control button is just floating, if there were a way to link that button to a specific cell, then anytime that button is pressed, we would know that the row above that link is the row to be copied.

But because the button is floating, I dont know if thats possible???

 

[Will85]

If I am failing at explaining, I apologize. I can upload a file if my explanation does not sufice.

Basically, I have account numbers, for example Office Expense. The user may need multiple rows of "Office Expense". One for coffee, one for paper towels, one for pencils, etc. etc. The next account might be "Utilities". They would also need multiple utilities rows, one for electricity, one for gas, one for water, etc etc.

They might need 10 Office expense rows, and 3 utilitiy rows. I have no idea how many. So I have buttons for each expense, so that they can press a button and a new blank Office Expense row with all that specific account information (General Ledger number, etc) is created. I have sub-totals for each expense, and the button is next to each sub-total and each macro is specific to that expense. Because I never know where a specific account number subtotal will be in a spreadsheet, because they are constantly adding and deleting rows, I have an "x" in column A next to each sub-total of each account, otherwise all other cells in column A are blank. There are over 200 accounts. So for the 200th account, I know that if I xldown 200 times, Ill land on the 200th account and the macro will copy that account and paste it.

Instead of putting xldown 200 times in my macro, Id rather be able to say xldown Y number of times, and tell it too look up the value of Y based on the account number (hardcoded in the macro) on another sheet, where I will track the position of each account.

This current logic requires that I have 200 macros, with 200 hardcoded account numbers to lookup, but I have'nt gotten far enough along to address that problem. Ideally, the macro will know which form control button I am clicking (because there is one for each account) and therefore know which account I am adding, but I havent gotten that far.

Not that I can think of, its completely user dependent on what account number they want to duplicate.

In the row below each x, I have a sub-total, next to the subtotal I have manually inserted a form control button (which activates the macro for that account).

My form control button is just floating, if there were a way to link that button to a specific cell, then anytime that button is pressed, we would know that the row above that link is the row to be copied.

But because the button is floating, I dont know if thats possible???

 

[Fluff]

In the properties, if you set "Move with cells" you could use something like

Sub Will85()
   Dim Shp As Shape
   Set Shp = ActiveSheet.Shapes(Application.Caller)
      Shp.TopLeftCell.Offset(-1).EntireRow.Copy
End Sub

 

[Will85]

Oooh, how exciting. This could work. Id have to get crafty with my protection and build it into the macro to unprotect when its copying and inserting, and reprotect, so that a user could not move the button on accident.

Ill play with this and report back.

Thank you.

In the properties, if you set "Move with cells" you could use something like

Sub Will85()
   Dim Shp As Shape
   Set Shp = ActiveSheet.Shapes(Application.Caller)
      Shp.TopLeftCell.Offset(-1).EntireRow.Copy
End Sub

 

[Will85]

I keep getting an error.

"You cant paste this here because the Copy area nd paste are arent the same size.

Select just one cell in the past area or an area thats the same size, and try pasting again"

In the properties, if you set "Move with cells" you could use something like

Sub Will85()
   Dim Shp As Shape
   Set Shp = ActiveSheet.Shapes(Application.Caller)
      Shp.TopLeftCell.Offset(-1).EntireRow.Copy
End Sub

 

[Will85]

Ahhh, problem. The current active cell (wherever the user may have last clicked before clicking the button) is likely not going to be in the correct column (column a) or the correct row (the row where the button is located), which I think is causing the error.

When I click the button, I need the active cell to move to the same row as the button and column a, in order to paste the selected row properly.

Thoughts?