Copy/Paste Macro

[MFO_Jester]

I am having an issue with a macro I created. In a brand new workbook, the following works:

Please Login or Register  to view this content.

The problem I encounter is when trying to apply this to an existing workbook. I get a Run-Time Error '13': Type Mismatch.

It stops at the line ActCellVariable = Range(RngActCell).Value.

I have tried to use the If Error Resume Next and it keeps the dialog box from popping up but does not copy the value into the new cell.

Can someone please help?

[TMS]

So, presumably, the cell being copied has somthing in it that isn't a string.

Which cell is being copied and what is in it?

I'm guessing it is a formula with an error condition lik #DIV/0! or #N/A

Regards

[MFO_Jester]

The cell's themselves that I want to copy are formatted as follows:

AA1234
or
123456

The thing that might be causing the error is the cell directly above them has a VLOOKUP that references the cell I'm trying to copy.

I would post a copy of the workbook but I can't because my work won't let me.

* Edit *

I just checked and there are other areas of the workbook that the macro works so it is the VLOOKUP in the cell above that is causing the error.

[TMS]

Can you put together a small sample withe the cell that you want to copy and the cell that references it? If you can simulate the problem, can you post the sample workbook? From work or home.

I can reproduce the problem if the cell being copied has a #DIV/0! in it. Can't see why another cell would hiccup.

Happy to look at it for you but, at the moment, there's not much I can suggest.

Regards

[MFO_Jester]

Alright... Attached is a severely dumbed down version of the workbook.

I essentially want to use the macro to copy/paste the cell with "12345" to the cell directly to the right.

Let me know what you can figure out.

Thanks,

[TMS]

OK. The value 12345 is in a merged pair of cells, A2:B2. When you run the macro and get the address of the selection, it picks up $A2:$B2.

So, Excel is unhappy about trying to copy two cells' values into one string variable.

Try:

Please Login or Register  to view this content.

Or get shut of the merged cells ... which would be my recommendation.


Regards

[MFO_Jester]

Thanks that worked.

I appreciate all of your help.

[TMS]

You're welcome.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save