Relative reference problem
 

Hi,
I'd like to start a macro by clicking on a macro button, which copies a couple of cells. The values of these copied cells have to be pasted as values to the active cell.

E.g.: the active cell is C46. Now I press the button which copies cell AG23:AR23.
The values of AG23:AR23 are to be copied into C46:N46

The active cell is changing all the time though, therefore the cell reference has to be kept relative.

Thanks in advance,
Norbert

Relative reference problem
 

Hi Norbert,

Am Thu, 30 Aug 2018 05:59:56 -0700 (PDT) schrieb Norbert:

E.g.: the active cell is C46. Now I press the button which copies cell AG23:AR23.
The values of AG23:AR23 are to be copied into C46:N46

try:

Private Sub CommandButton1_Click()
Dim varData As Variant

With ActiveCell
varData = Range("AG23:AR23")
.Resize(1, UBound(varData, 2)).Value = varData
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016

Relative reference problem
 

Hi Claus,
thank you very much! It works!

How do I get the active cell to be one cell further down, after I ran the code?

E.g., the active cell is C46. I click on the command button. Now I want the active
cell to be C47 (relative reference, one a cell further down than before)

Regards,
Norbert

Relative reference problem
 

Hi Norbert,

Am Thu, 30 Aug 2018 08:11:40 -0700 (PDT) schrieb Norbert:

How do I get the active cell to be one cell further down, after I ran the code?

E.g., the active cell is C46. I click on the command button. Now I want the active
cell to be C47 (relative reference, one a cell further down than before)

try:

Private Sub CommandButton1_Click()
Dim varData As Variant
Dim myCell As Range

Set myCell = ActiveCell
With myCell
varData = Range("AG23:AR23")
.Resize(1, UBound(varData, 2)).Value = varData
Set myCell = myCell.Offset(1)
myCell.Select
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016

Relative reference problem
 

Hi again,

Am Thu, 30 Aug 2018 17:19:28 +0200 schrieb Claus Busch:

Private Sub CommandButton1_Click()

better try:

Private Sub CommandButton1_Click()
Dim varData As Variant
Dim myCell As Range

Set myCell = ActiveCell
With myCell
varData = Range("AG23:AR23")
.Resize(1, UBound(varData, 2)).Value = varData
Application.Goto myCell.Offset(1)
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016

Relative reference problem
 

Hi Claus,

that works perfect! Thank you very much!
Just a small little extra maybe: After I pressed the command button, the code runs, the cell below the active cell is selected but the focus is still on the command button (I can't control the active cell with my cursor keys).
Is there a way to set the focus onto the active cell?

Best regards,
Norbert

Relative reference problem
 

Hi Claus,

that works perfect! Thank you very much!
Just a small little extra maybe: After I pressed the command button, the code
runs, the cell below the active cell is selected but the focus is still on
the command button (I can't control the active cell with my cursor keys). Is
there a way to set the focus onto the active cell?

Best regards,
Norbert

Couple of ways w/o a button...
You could assign a keyboard shortcut key combo to the macro and use that
instead of a button control.

Delete the button;
disguise a cell (or merged cells) to look like a button;
use the SelectionChange event to fire the macro when you select the cell
that resembles a button.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion