Solved Change an instruction in a macro

April 17, 2013 at 12:52:33
Specs: Windows 7
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

See More: Change an instruction in a macro

Report •

✔ Best Answer
April 20, 2013 at 05:57:33
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.



#1
April 17, 2013 at 14:15:59
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"startcellnum

Then Continue your sub where I left off. You can then use the variable 'cellname' to refer to the cell you want to start at.


Report •

#2
April 17, 2013 at 20:27:23
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.


Report •

#3
April 19, 2013 at 09:36:02
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 follows

Block 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


Report •

Related Solutions

#4
April 19, 2013 at 10:18:23
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.


Report •

#5
April 19, 2013 at 12:17:23
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

Report •

#6
April 19, 2013 at 12:28:49
Hi,
Just to add a bit extra, instead of the "K1" changing to " K10", the number part of the cell position stays as "?1"

Report •

#7
April 19, 2013 at 12:40:05
I'll try one more time.

Let me ask my question in a different way:

The MATCH function returns a number. How does that number help 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.


Report •

#8
April 19, 2013 at 19:43:11
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

Report •

#9
April 20, 2013 at 05:57:33
✔ 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.


Report •

#10
April 20, 2013 at 08:47:49
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............................amazing

Thank you
TonyGibb27


Report •

#11
April 20, 2013 at 13:55:41
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.


Report •

Ask Question