Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with relative reference. | Excel Programming | |||
VBA problem for forumla with relative reference | Excel Worksheet Functions | |||
i've tried every thing relative reference problem. | Excel Programming | |||
Using an offset formula for the reference in a relative reference | Excel Worksheet Functions | |||
problem with relative reference | Excel Programming |