Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old August 30th 18, 01:59 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2012
Posts: 13
Default 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

  #2   Report Post  
Old August 30th 18, 02:14 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,625
Default 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
  #3   Report Post  
Old August 30th 18, 04:11 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2012
Posts: 13
Default 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
  #4   Report Post  
Old August 30th 18, 04:19 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,625
Default 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
  #5   Report Post  
Old August 30th 18, 04:21 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,625
Default 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


  #6   Report Post  
Old August 31st 18, 10:41 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2012
Posts: 13
Default 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
  #7   Report Post  
Old August 31st 18, 09:34 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 959
Default 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem with relative reference. Roontoon Excel Programming 1 May 10th 12 03:36 AM
VBA problem for forumla with relative reference Joanne Excel Worksheet Functions 2 December 9th 08 02:20 PM
i've tried every thing relative reference problem. Tomkat743 Excel Programming 1 February 3rd 07 10:02 AM
Using an offset formula for the reference in a relative reference Cuda Excel Worksheet Functions 6 November 15th 06 06:12 PM
problem with relative reference Herman Excel Programming 1 May 18th 05 07:14 PM


All times are GMT +1. The time now is 10:45 AM.

Powered by vBulletin® Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017