Hello,

In VBA I would like to find the cell to the right of a merged cell. (or its
column-number)
The column of the cell to the right is not just the column of the ActiveCell
+ 1, but it depends on who many columns are merged!

Example:

In row 1 Cell A and B are merged. (A1 and B1 are merged)

The user can select this merged cell. Its name is A1. (B1 is not existing,
as it is merged to A1).

In VBA find now the Column of the cell to the right. In the sheet it is C1.
(so VBA should give column=3 for C).

What is the code for this?

ActiveCell.Next gives 2, means "B", this is wrong, this cell is not
existing.


Thanks for any help
Marcel

Something like this will return the address of the next cell to the right of
a merged group of cells...

Range("A1").MergeArea.Offset(0, 1).Address

--

Regards,
Nigel




"Marcel" wrote in message
...
Hello,

In VBA I would like to find the cell to the right of a merged cell. (or
its column-number)
The column of the cell to the right is not just the column of the
ActiveCell + 1, but it depends on who many columns are merged!

Example:

In row 1 Cell A and B are merged. (A1 and B1 are merged)

The user can select this merged cell. Its name is A1. (B1 is not existing,
as it is merged to A1).

In VBA find now the Column of the cell to the right. In the sheet it is
C1. (so VBA should give column=3 for C).

What is the code for this?

ActiveCell.Next gives 2, means "B", this is wrong, this cell is not
existing.


Thanks for any help
Marcel

Hello Marcel,

This is what you need:

NextCol=ActiveCell.MergeArea.Offset(0, 1).Column

Regards,
Per

"Marcel" skrev i meddelelsen
...
Hello,

In VBA I would like to find the cell to the right of a merged cell. (or
its column-number)
The column of the cell to the right is not just the column of the
ActiveCell + 1, but it depends on who many columns are merged!

Example:

In row 1 Cell A and B are merged. (A1 and B1 are merged)

The user can select this merged cell. Its name is A1. (B1 is not existing,
as it is merged to A1).

In VBA find now the Column of the cell to the right. In the sheet it is
C1. (so VBA should give column=3 for C).

What is the code for this?

ActiveCell.Next gives 2, means "B", this is wrong, this cell is not
existing.


Thanks for any help
Marcel

Thanks,

But when I use "Delete" on the Merged Cell A1 (=A1+B1) I get an exception on
Range("A1").MergeArea.

But Range("A1").Offset(0, 1) works (without .MergeArea)

Marcel


"Nigel" wrote in message
...
Something like this will return the address of the next cell to the right
of a merged group of cells...

Range("A1").MergeArea.Offset(0, 1).Address

--

Regards,
Nigel




"Marcel" wrote in message
...
Hello,

In VBA I would like to find the cell to the right of a merged cell. (or
its column-number)
The column of the cell to the right is not just the column of the
ActiveCell + 1, but it depends on who many columns are merged!

Example:

In row 1 Cell A and B are merged. (A1 and B1 are merged)

The user can select this merged cell. Its name is A1. (B1 is not
existing, as it is merged to A1).

In VBA find now the Column of the cell to the right. In the sheet it is
C1. (so VBA should give column=3 for C).

What is the code for this?

ActiveCell.Next gives 2, means "B", this is wrong, this cell is not
existing.


Thanks for any help
Marcel

Thanks,

But when I use "Delete" on the Merged Cell A1 (=A1+B1) I get an exception on
Range("A1").MergeArea.

But Range("A1").Offset(0, 1) works (without .MergeArea)

Marcel
"Per Jessen" wrote in message
...
Hello Marcel,

This is what you need:

NextCol=ActiveCell.MergeArea.Offset(0, 1).Column

Regards,
Per

"Marcel" skrev i meddelelsen
...
Hello,

In VBA I would like to find the cell to the right of a merged cell. (or
its column-number)
The column of the cell to the right is not just the column of the
ActiveCell + 1, but it depends on who many columns are merged!

Example:

In row 1 Cell A and B are merged. (A1 and B1 are merged)

The user can select this merged cell. Its name is A1. (B1 is not
existing, as it is merged to A1).

In VBA find now the Column of the cell to the right. In the sheet it is
C1. (so VBA should give column=3 for C).

What is the code for this?

ActiveCell.Next gives 2, means "B", this is wrong, this cell is not
existing.


Thanks for any help
Marcel

It is not clear from your two posts what you are trying to do. My guess is
you want to delete the contents of the cell to the right of the merged
cells. If that is the case, then this works for me...

Range("A1").MergeArea.Offset(,1).Delete

--
Rick (MVP - Excel)


"Marcel" wrote in message
...
Thanks,

But when I use "Delete" on the Merged Cell A1 (=A1+B1) I get an exception
on Range("A1").MergeArea.

But Range("A1").Offset(0, 1) works (without .MergeArea)

Marcel


"Nigel" wrote in message
...
Something like this will return the address of the next cell to the right
of a merged group of cells...

Range("A1").MergeArea.Offset(0, 1).Address

--

Regards,
Nigel




"Marcel" wrote in message
...
Hello,

In VBA I would like to find the cell to the right of a merged cell. (or
its column-number)
The column of the cell to the right is not just the column of the
ActiveCell + 1, but it depends on who many columns are merged!

Example:

In row 1 Cell A and B are merged. (A1 and B1 are merged)

The user can select this merged cell. Its name is A1. (B1 is not
existing, as it is merged to A1).

In VBA find now the Column of the cell to the right. In the sheet it is
C1. (so VBA should give column=3 for C).

What is the code for this?

ActiveCell.Next gives 2, means "B", this is wrong, this cell is not
existing.


Thanks for any help
Marcel

Hello Rick,

The user Selects the (Merged) Cell "A1" and deletes its content. (with the
"delete" button on the keyboard).
This fires my function Workbook_SheetChange.
In this function I would like to know the column of the next cell to the
right, but my function makes an exception on the
second Line does an exception, first not!

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Debug.Print Target .Offset(0, 1).Column
Debug.Print Target .MergeArea.Offset(0, 1).Column ' here I get an
exception!!
end sub



Tkx
Marcel

"Rick Rothstein" wrote in message
...
It is not clear from your two posts what you are trying to do. My guess is
you want to delete the contents of the cell to the right of the merged
cells. If that is the case, then this works for me...

Range("A1").MergeArea.Offset(,1).Delete

--
Rick (MVP - Excel)


"Marcel" wrote in message
...
Thanks,

But when I use "Delete" on the Merged Cell A1 (=A1+B1) I get an exception
on Range("A1").MergeArea.

But Range("A1").Offset(0, 1) works (without .MergeArea)

Marcel


"Nigel" wrote in message
...
Something like this will return the address of the next cell to the
right of a merged group of cells...

Range("A1").MergeArea.Offset(0, 1).Address

--

Regards,
Nigel




"Marcel" wrote in message
...
Hello,

In VBA I would like to find the cell to the right of a merged cell.
(or its column-number)
The column of the cell to the right is not just the column of the
ActiveCell + 1, but it depends on who many columns are merged!

Example:

In row 1 Cell A and B are merged. (A1 and B1 are merged)

The user can select this merged cell. Its name is A1. (B1 is not
existing, as it is merged to A1).

In VBA find now the Column of the cell to the right. In the sheet it is
C1. (so VBA should give column=3 for C).

What is the code for this?

ActiveCell.Next gives 2, means "B", this is wrong, this cell is not
existing.


Thanks for any help
Marcel