selecting a cell - really need help

[JasonK]

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

[Nigel]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[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"
<[email protected]> 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

[broro183]

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://www.google.co.nz/search?num=1...ell.%22+&meta=

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 Format>Cells>Alignment>Horizontal>Center 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]

?? - 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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"
> <[email protected]> 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
>

[JasonK]

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
<[email protected]> 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
>Format>Cells>Alignment>Horizontal>Center 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...

[JasonK]

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
><[email protected]> 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
>>Format>Cells>Alignment>Horizontal>Center 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]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
> ><[email protected]> 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
> >>Format>Cells>Alignment>Horizontal>Center 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...
>

[JasonK]

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"
<[email protected]> 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.