TIA boys and girls. i've been working on this one in other newsgroups
for quite a while and i just can't seem to make a macro work for me.

i have cells on the left side of a spreadsheet that have different
numerical data in them.

i have blank merged cells on the right side of a spreadsheet.

i have a working macro that will determine a number (a variable),
assign that number to a variable (x), then search for that number in
the numerical cells on the left side of the spread sheet, possibly
finding them anywhere.

i can select the cell with the number in it, and copy it to the
clipboard using a macro. but........... i can't paste the value of the
cell, or the value of the variable (the same value) to a cell on the
right because the cells on the right are merged cells and i get the
error, "cannot change part of a merged cell." the new cell (on the
right) will not be in a particular place so that i can reference it by
range, but will be in a position relative to the cell on the left
where the number was found. I can physically cut and paste myself
with out the macro, but when i record that, the macro recording
reference is to a specific cell that i can see is the correct one.

i tried using offset, but the error msg shows here too.

i need to be able to select and activate a cell 5 cells to the right
of some other cell, the left cell being random so that i can not call
it by address. if i can activate the destination cell, i can figure
out how to paste the number (similar to a keyboard cut and paste) and
the new cell should take the variable or the value from the preceeding
cell. activecell.value = x or something like that. i have to figure
how to make the cell 5 cells over the active cell.

i just can't make it select the destination cell. can anyone tell me
how to do this?


i hope this is clear,
thanks
jasonk

The copy / paste method requires the target area to be the same size as the
source. So assign the source to a variable and directly place this in the
offset column ........

Dim x As Variant
x = ActiveCell.Value
ActiveCell.Offset(0, 5) = x


--
Cheers
Nigel



"JasonK" wrote in message
...
TIA boys and girls. i've been working on this one in other newsgroups
for quite a while and i just can't seem to make a macro work for me.

i have cells on the left side of a spreadsheet that have different
numerical data in them.

i have blank merged cells on the right side of a spreadsheet.

i have a working macro that will determine a number (a variable),
assign that number to a variable (x), then search for that number in
the numerical cells on the left side of the spread sheet, possibly
finding them anywhere.

i can select the cell with the number in it, and copy it to the
clipboard using a macro. but........... i can't paste the value of the
cell, or the value of the variable (the same value) to a cell on the
right because the cells on the right are merged cells and i get the
error, "cannot change part of a merged cell." the new cell (on the
right) will not be in a particular place so that i can reference it by
range, but will be in a position relative to the cell on the left
where the number was found. I can physically cut and paste myself
with out the macro, but when i record that, the macro recording
reference is to a specific cell that i can see is the correct one.

i tried using offset, but the error msg shows here too.

i need to be able to select and activate a cell 5 cells to the right
of some other cell, the left cell being random so that i can not call
it by address. if i can activate the destination cell, i can figure
out how to paste the number (similar to a keyboard cut and paste) and
the new cell should take the variable or the value from the preceeding
cell. activecell.value = x or something like that. i have to figure
how to make the cell 5 cells over the active cell.

i just can't make it select the destination cell. can anyone tell me
how to do this?


i hope this is clear,
thanks
jasonk

I don't know why Nigel, but that didn't work. i did not receive an
error msg, but it didn't paste the variable value into the new cell
either.

i have no idea what to do.

i thank you for your time and appreciate any other suggestions though.

thanks again,
jasonk


On Wed, 15 Mar 2006 07:59:22 -0000, "Nigel"
wrote:

The copy / paste method requires the target area to be the same size as the
source. So assign the source to a variable and directly place this in the
offset column ........

Dim x As Variant
x = ActiveCell.Value
ActiveCell.Offset(0, 5) = x

?? - it should do

If you did not get an error then it is probably working, but you either have
not selected an active cell with something in it or the target cell format
is hiding the value.

Paste the code section you are using......

--
Cheers
Nigel



"JasonK" wrote in message
...

I don't know why Nigel, but that didn't work. i did not receive an
error msg, but it didn't paste the variable value into the new cell
either.

i have no idea what to do.

i thank you for your time and appreciate any other suggestions though.

thanks again,
jasonk


On Wed, 15 Mar 2006 07:59:22 -0000, "Nigel"
wrote:

The copy / paste method requires the target area to be the same size as
the
source. So assign the source to a variable and directly place this in
the
offset column ........

Dim x As Variant
x = ActiveCell.Value
ActiveCell.Offset(0, 5) = x

Hi Jason,

Is there a real need for the cells on the right to be merged?
I don't like merged cells but thought I'd do a couple of quick Googles
& see what others think, eg
http://tinyurl.com/g7evk

I came across the below and think Barry Katcher's explanation is very
apt & I agree with his suggestion:

"the technical explanation of what is happening is this: merged cells
suck! They're much more trouble than they're worth. Before trying to
figure out code to circumvent your problems, you might want to try
this: unmerge your cells, and try formatting them to
FormatCellsAlignmentHorizontalCenter Across Selection. Practically
the same results, without the hassle. "
http://www.mrexcel.com/archive2/72100/83644.htm

It seems the general consensus is to remove any merged cell formatting
& with it your problems will disappear. Probably not what you wnat to
hear but I hope it helps,

Rob Brockett
NZ
Always learning & the best way to learn is to experience...


--
broro183
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=522524

Jason,
you're macro works great stand alone. you were right. the merged cells
are the problem. for this worksheet, i need them though.

is it possible to select the cell that is 5 cells over? or can i only
reference it? if i can select it, then i can paste, and that seems to
work at least manually, outside a macro.

thanks again,
jasonk





On Wed, 15 Mar 2006 02:33:03 -0600, broro183
wrote:


Hi Jason,

Is there a real need for the cells on the right to be merged?
I don't like merged cells but thought I'd do a couple of quick Googles
& see what others think, eg
http://tinyurl.com/g7evk

I came across the below and think Barry Katcher's explanation is very
apt & I agree with his suggestion:

"the technical explanation of what is happening is this: merged cells
suck! They're much more trouble than they're worth. Before trying to
figure out code to circumvent your problems, you might want to try
this: unmerge your cells, and try formatting them to
FormatCellsAlignmentHorizontalCenter Across Selection. Practically
the same results, without the hassle. "
http://www.mrexcel.com/archive2/72100/83644.htm

It seems the general consensus is to remove any merged cell formatting
& with it your problems will disappear. Probably not what you wnat to
hear but I hope it helps,

Rob Brockett
NZ
Always learning & the best way to learn is to experience...

Nigel,
you're macro works great stand alone. you were right. the merged cells
are the problem. for this worksheet, i need them though.

is it possible to select the cell that is 5 cells over? or can i only
reference it? if i can select it, then i can paste, and that seems to
work at least manually, outside a macro.

thanks again,
jasonk


Dim x As Variant
x = ActiveCell.Value
ActiveCell.Offset(0, 5) = x







On Wed, 15 Mar 2006 02:33:03 -0600, broro183
wrote:


Hi Jason,

Is there a real need for the cells on the right to be merged?
I don't like merged cells but thought I'd do a couple of quick Googles
& see what others think, eg
http://tinyurl.com/g7evk

I came across the below and think Barry Katcher's explanation is very
apt & I agree with his suggestion:

"the technical explanation of what is happening is this: merged cells
suck! They're much more trouble than they're worth. Before trying to
figure out code to circumvent your problems, you might want to try
this: unmerge your cells, and try formatting them to
FormatCellsAlignmentHorizontalCenter Across Selection. Practically
the same results, without the hassle. "
http://www.mrexcel.com/archive2/72100/83644.htm

It seems the general consensus is to remove any merged cell formatting
& with it your problems will disappear. Probably not what you wnat to
hear but I hope it helps,

Rob Brockett
NZ
Always learning & the best way to learn is to experience...

Generally you do not need to use select - activate or copy-paste, by
referencing the cell(s) directly you can assign a value to / from a
variable. It is faster and will overcome the merged cell reference problem
you are having. The problem is that you probably have a mixture of
cut-paste / assignments and that is throwing the errors.

As I said post your section of code and someone can advise on what to do.

--
Cheers
Nigel



"JasonK" wrote in message
...
Nigel,
you're macro works great stand alone. you were right. the merged cells
are the problem. for this worksheet, i need them though.

is it possible to select the cell that is 5 cells over? or can i only
reference it? if i can select it, then i can paste, and that seems to
work at least manually, outside a macro.

thanks again,
jasonk


Dim x As Variant
x = ActiveCell.Value
ActiveCell.Offset(0, 5) = x







On Wed, 15 Mar 2006 02:33:03 -0600, broro183
wrote:


Hi Jason,

Is there a real need for the cells on the right to be merged?
I don't like merged cells but thought I'd do a couple of quick Googles
& see what others think, eg
http://tinyurl.com/g7evk

I came across the below and think Barry Katcher's explanation is very
apt & I agree with his suggestion:

"the technical explanation of what is happening is this: merged cells
suck! They're much more trouble than they're worth. Before trying to
figure out code to circumvent your problems, you might want to try
this: unmerge your cells, and try formatting them to
FormatCellsAlignmentHorizontalCenter Across Selection. Practically
the same results, without the hassle. "
http://www.mrexcel.com/archive2/72100/83644.htm

It seems the general consensus is to remove any merged cell formatting
& with it your problems will disappear. Probably not what you wnat to
hear but I hope it helps,

Rob Brockett
NZ
Always learning & the best way to learn is to experience...

Nigel,
I got that working. Thanks for your help
it was a question of referencing the proper cell of the nine merged
cells. If i paste to the upper left corner, then it will paste
properly, so i had to write a few extra lines, if x = 1 then (0,4)....
if x = 2, then (-1, 4) or whatever, you get the point.

thanks for your help though. i'm learning a lot.

jasonk






On Thu, 16 Mar 2006 10:21:02 -0000, "Nigel"
wrote:

Generally you do not need to use select - activate or copy-paste, by
referencing the cell(s) directly you can assign a value to / from a
variable. It is faster and will overcome the merged cell reference problem
you are having. The problem is that you probably have a mixture of
cut-paste / assignments and that is throwing the errors.

As I said post your section of code and someone can advise on what to do.