Hi,

I've got the following scenario:

a user runs a macro I've provided, which among others copies a range of
cells of the same row and pastes it in the row above. After that, the macro
displays a message box prompting him to type his name into one (always the
seventh) of the pasted cells.

Well, is there a way to display in the message box the equivalent name of
the cell for any macro's use?
That means, if the copied range is A10:K10, the pasted range will be A9:K9
so, the cell for user's name will be the G9, and the message box will show:
"Please, type your name in cell G9"

while, if the copied range is R4:AB4, the pasted range will be R3:AB3 so,
the cell for his name will be the X3, and the message box will show:
"Please, type your name in cell X3"

Thanks
--
Merlin dying to the Lady of the Lake:
"We lived our lives with passion and devotion"
--
Please, remove the TRAP to contact me
--

It can be done, but to answer fully we would need to know how you do the
copy/paste. Here is a suggestion

If you use a range object to do the past, say oRng which points at A9, then
use

Msgbox "Please, type your name in cell " &
oRng.Offset(0,6).Address(False,False)

--

HTH

RP

"geotso" wrote in message
...
Hi,

I've got the following scenario:

a user runs a macro I've provided, which among others copies a range of
cells of the same row and pastes it in the row above. After that, the
macro
displays a message box prompting him to type his name into one (always the
seventh) of the pasted cells.

Well, is there a way to display in the message box the equivalent name of
the cell for any macro's use?
That means, if the copied range is A10:K10, the pasted range will be A9:K9
so, the cell for user's name will be the G9, and the message box will
show:
"Please, type your name in cell G9"

while, if the copied range is R4:AB4, the pasted range will be R3:AB3 so,
the cell for his name will be the X3, and the message box will show:
"Please, type your name in cell X3"

Thanks
--
Merlin dying to the Lady of the Lake:
"We lived our lives with passion and devotion"
--
Please, remove the TRAP to contact me
--

Yes, it might work.
I'll try it tomorrow in the office and let you know.

Thanks Bob

Bob Phillips wrote:
It can be done, but to answer fully we would need to know how you do
the copy/paste. Here is a suggestion

If you use a range object to do the past, say oRng which points at
A9, then use

Msgbox "Please, type your name in cell " &
oRng.Offset(0,6).Address(False,False)


"geotso" wrote in message
...
Hi,

I've got the following scenario:

a user runs a macro I've provided, which among others copies a range
of cells of the same row and pastes it in the row above. After that,
the macro displays a message box prompting him to type his name into
one (always the seventh) of the pasted cells.

Well, is there a way to display in the message box the equivalent
name of the cell for any macro's use?
That means, if the copied range is A10:K10, the pasted range will be
A9:K9 so, the cell for user's name will be the G9, and the message
box will show: "Please, type your name in cell G9"

while, if the copied range is R4:AB4, the pasted range will be
R3:AB3 so, the cell for his name will be the X3, and the message box
will show: "Please, type your name in cell X3"

Thanks
--
Merlin dying to the Lady of the Lake:
"We lived our lives with passion and devotion"
--
Please, remove the TRAP to contact me
--

If it doesn't, post the code to help.

--

HTH

RP

"geotso" wrote in message
...
Yes, it might work.
I'll try it tomorrow in the office and let you know.

Thanks Bob

Bob Phillips wrote:
It can be done, but to answer fully we would need to know how you do
the copy/paste. Here is a suggestion

If you use a range object to do the past, say oRng which points at
A9, then use

Msgbox "Please, type your name in cell " &
oRng.Offset(0,6).Address(False,False)


"geotso" wrote in message
...
Hi,

I've got the following scenario:

a user runs a macro I've provided, which among others copies a range
of cells of the same row and pastes it in the row above. After that,
the macro displays a message box prompting him to type his name into
one (always the seventh) of the pasted cells.

Well, is there a way to display in the message box the equivalent
name of the cell for any macro's use?
That means, if the copied range is A10:K10, the pasted range will be
A9:K9 so, the cell for user's name will be the G9, and the message
box will show: "Please, type your name in cell G9"

while, if the copied range is R4:AB4, the pasted range will be
R3:AB3 so, the cell for his name will be the X3, and the message box
will show: "Please, type your name in cell X3"

Thanks
--
Merlin dying to the Lady of the Lake:
"We lived our lives with passion and devotion"
--
Please, remove the TRAP to contact me
--

geotso,

Why not have an input box in your macro asking for a name and then paste
that name in the cell within your macro.
UserName = inputbox("Type your name!","Name Required")

Neil

"geotso" wrote in message
...
Hi,

I've got the following scenario:

a user runs a macro I've provided, which among others copies a range of
cells of the same row and pastes it in the row above. After that, the
macro
displays a message box prompting him to type his name into one (always the
seventh) of the pasted cells.

Well, is there a way to display in the message box the equivalent name of
the cell for any macro's use?
That means, if the copied range is A10:K10, the pasted range will be A9:K9
so, the cell for user's name will be the G9, and the message box will
show:
"Please, type your name in cell G9"

while, if the copied range is R4:AB4, the pasted range will be R3:AB3 so,
the cell for his name will be the X3, and the message box will show:
"Please, type your name in cell X3"

Thanks
--
Merlin dying to the Lady of the Lake:
"We lived our lives with passion and devotion"
--
Please, remove the TRAP to contact me
--