Hi there

I am trying to paste a block of data (A1:I31) along the worksheet using the match function to dictate how far along the paste needs to be.

When I record a macro to copy and paste, the Range is "K1"

I need to change this instruction to use the figure generated in a static cell to determine the position of the paste, instead of "K1", 10, or 20, or 50

The figure is generated using a MATCH formula

Any ideas as I am getting a headache

✔ Best Answer

You can use the Cells property of the Range object. Cells(row_index, column_index)Cells(1, 1) is the same as Range("A1")

Cells(5, 11) is the same as Range("K5")

To use a value in a cell as one of the index values use:

Cells(1, Range("A1"))See here for more details on the Range object and the Cells property:

http://www.anthony-vba.kefra.com/vb...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

You may be able to use something like this?: dim startcellnum as number dim cellname as string startcellnum = InputBox("Please Enter Cell Number to Start Paste", "You Must Enter a Number!") If Trim(startcellnum) = "" Then MsgBox "You did not enter a Number. Paste cancled." exit sub Else set cellname = "K"startcellnumThen Continue your sub where I left off. You can then use the variable 'cellname' to refer to the cell you want to start at.

If the value returned by the MATCH function is in e.g. A1, you should be able to use something like this: Range("K" & Range("A1"))e.g. if A1 contains 20, that line will resolve to:

Range("K20"))

Depending on what your code is doing, you may need to include Sheet Names:Range("K" & Sheets("MySheetName").Range("A1"))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Hi DerbyDad03,

I like what that does but I require slightly different. With your instruction the paste occurs to "K20". What I require is as followsBlock of data "A1:I31" is a constant where only certain fields change

I need to copy and paste the block of data, subject to the number generated by a match formula, across the worksheet

For example, one paste could be to "K1", the next paste could be to "BA1"Any thoughts or ideas

Cheers

TonyGibb27

I don't understand what you mean by "subject to the number generated by a match formula" and how that would be used to differentiate between K1 and BA1. In your OP you said:

"I need to change this instruction to use the figure generated in a static cell to determine the position of the paste, instead of "K1", 10, or 20, or 50"By "figure" I assumed you meant "number", therefore I assumed you meant K1, K10, K20 or K50.

Now I have no idea what you mean.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Hi,

Sorry but I need to cut across the worksheet and not DOWN, ie not "K10", "K20" or "K50", but across the worksheet, "K1", "Y1", or "AB1"

It is probably a simple instruction but I am total flummoxed

Hi,

Just to add a bit extra, instead of the "K1" changing to " K10", the number part of the cell position stays as "?1"

I'll try one more time. Let me ask my question in a different way:

The MATCH function returns a

number. How does thatnumberhelp you determine whether the paste column is K or Y or AB?Does 11 mean Column K, 25 mean Column Y, 28 mean Column AB, etc?

Keep in mind that I can't see your spreadsheet from where I'm sitting nor can I read your mind. You said:

"I need to change this instruction to use the figure generated in a static cell to determine the position of the paste, instead of "K1", 10, or 20, or 50.

The figure is generated using a MATCH formula."OK, so let's say the MATCH function returned 50. Does that mean Column 50 i.e. Column AX?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Hi, Your assumption is correct.

If the Match function has returned 50, that means Column 50 or AX1

Apologies for not explaining this clearly, but you are spot on with your assumption

You can use the Cells property of the Range object. Cells(row_index, column_index)Cells(1, 1) is the same as Range("A1")

Cells(5, 11) is the same as Range("K5")

To use a value in a cell as one of the index values use:

Cells(1, Range("A1"))See here for more details on the Range object and the Cells property:

http://www.anthony-vba.kefra.com/vb...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

DerbyDad03

You are a master in patience. What you have given me works outstandingly well and has cut down a labour intensive copy and paste process to the click of a button.

Cannot thank you enough............................amazingThank you

TonyGibb27

I'm glad I could help. I also hope you learned a little more than just some VBA syntax via this thread. I hope you now see that the more details you provide the easier it is for us to provide a solution. Had your first post included something like "I need the value returned by the MATCH function to

represent a column" we probably could have had this solved in 2 or 3 posts instead of 10.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll

Did you buy anything on Amazon's Prime Day?

Discuss in The Lounge

Poll History