Problem with xldown using relative reference

I

iujfcra

New Member
Joined
Feb 20, 2018
Messages
4
Can anyone help me with this code? The range is only selecting 2 cells down from the active cell each time, instead of to the last cell with data.

Code: 
    Selection.Copy
    ActiveCell.Offset(1, 0).Range("A1", Range("A1").End(xlDown)).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(-1, 1).Range("A1").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveCell.Offset(1, 0).Range("A1", Range("A1").End(xlDown)).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(-2, 1).Range("A1").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveCell.Offset(1, 0).Range("A1", Range("A1").End(xlDown)).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Sort by date Sort by votes
Hi & welcome to the board
What does your data look like?
 
Last edited:
Upvote 0
Fluff said:
Hi & welcome to the board
What does your data look like?
Click to expand...

Hi. My data is in a rectangle and I am trying to copy down from a selection and then move up and to the right (forming a a step) and perform copy paste into a range below the step cell. I am new to VBA and have not been able to get this to work correctly.


[TABLE="width: 448"]
<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64"]0.00%[/TD]
[TD="class: xl63, width: 64"]0.00%[/TD]
[TD="class: xl63, width: 64"]0.00%[/TD]
[TD="class: xl63, width: 64"]0.00%[/TD]
[TD="class: xl63, width: 64"]0.00%[/TD]
[TD="class: xl64, width: 64"]5.00%[/TD]
[TD="class: xl65, width: 64"]6.00%[/TD]
[/TR]
[TR]
[TD="class: xl63"]0.00%[/TD]
[TD="class: xl63"]0.00%[/TD]
[TD="class: xl63"]0.00%[/TD]
[TD="class: xl63"]0.00%[/TD]
[TD="class: xl64"]4.00%[/TD]
[TD="class: xl65"]5.00%[/TD]
[TD="class: xl63"]6.00%[/TD]
[/TR]
[TR]
[TD="class: xl63"]0.00%[/TD]
[TD="class: xl63"]0.00%[/TD]
[TD="class: xl63"]0.00%[/TD]
[TD="class: xl64"]3.00%[/TD]
[TD="class: xl63"]4.00%[/TD]
[TD="class: xl63"]5.00%[/TD]
[TD="class: xl63"]6.00%[/TD]
[/TR]
[TR]
[TD="class: xl63"]0.00%[/TD]
[TD="class: xl63"]0.00%[/TD]
[TD="class: xl64"]2.00%[/TD]
[TD="class: xl63"]3.00%[/TD]
[TD="class: xl63"]4.00%[/TD]
[TD="class: xl63"]5.00%[/TD]
[TD="class: xl63"]6.00%[/TD]
[/TR]
[TR]
[TD="class: xl66"]0.00%[/TD]
[TD="class: xl64"]1.00%[/TD]
[TD="class: xl63"]2.00%[/TD]
[TD="class: xl63"]3.00%[/TD]
[TD="class: xl63"]4.00%[/TD]
[TD="class: xl63"]5.00%[/TD]
[TD="class: xl63"]6.00%[/TD]
[/TR]
[TR]
[TD="class: xl63"]0.00%[/TD]
[TD="class: xl63"]1.00%[/TD]
[TD="class: xl63"]2.00%[/TD]
[TD="class: xl63"]3.00%[/TD]
[TD="class: xl63"]4.00%[/TD]
[TD="class: xl63"]5.00%[/TD]
[TD="class: xl63"]6.00%[/TD]
[/TR]
[TR]
[TD="class: xl63"]0.00%[/TD]
[TD="class: xl63"]1.00%[/TD]
[TD="class: xl63"]0.00%[/TD]
[TD="class: xl63"]3.00%[/TD]
[TD="class: xl63"]4.00%[/TD]
[TD="class: xl63"]5.00%[/TD]
[TD="class: xl63"]0.00%[/TD]
[/TR]
[TR]
[TD="class: xl63"]1.00%[/TD]
[TD="class: xl63"]0.00%[/TD]
[TD="class: xl63"]0.00%[/TD]
[TD="class: xl63"]3.00%[/TD]
[TD="class: xl63"]4.00%[/TD]
[TD="class: xl63"]0.00%[/TD]
[TD="class: xl63"]0.00%[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Is that how your data looks before you run the macro? After you run the macro? Or what you want it to look like?
Also which cell is the ActiveCell when you run the macro?
 
Upvote 0
Fluff said:
Is that how your data looks before you run the macro? After you run the macro? Or what you want it to look like?
Also which cell is the ActiveCell when you run the macro?
Click to expand...

B15 is the ActiveCell which is copied to the range B16:B18. Then it will move to C14 and copy that percentage to C15:C18. I am using relative reference since the starting point will change each month and the macro will be used for different data sets.

[TABLE="width: 512"]
<colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="class: xl66, width: 64"]BEFORE[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl67"]A[/TD]
[TD="class: xl67"]B[/TD]
[TD="class: xl67"]C[/TD]
[TD="class: xl67"]D[/TD]
[TD="class: xl67"]E[/TD]
[TD="class: xl67"]F[/TD]
[TD="class: xl67"]G[/TD]
[/TR]
[TR]
[TD="class: xl68"]11[/TD]
[TD="class: xl65, width: 64, align: right"]0.00%[/TD]
[TD="class: xl65, width: 64, align: right"]0.00%[/TD]
[TD="class: xl65, width: 64, align: right"]0.00%[/TD]
[TD="class: xl65, width: 64, align: right"]0.00%[/TD]
[TD="class: xl65, width: 64, align: right"]0.00%[/TD]
[TD="class: xl69, width: 64, align: right"]5.00%[/TD]
[TD="class: xl65, width: 64, align: right"]6.00%[/TD]
[/TR]
[TR]
[TD="class: xl68"]12[/TD]
[TD="class: xl65, width: 64, align: right"]0.00%[/TD]
[TD="class: xl70, width: 64, align: right"]0.00%[/TD]
[TD="class: xl70, width: 64, align: right"]0.00%[/TD]
[TD="class: xl70, width: 64, align: right"]0.00%[/TD]
[TD="class: xl69, width: 64, align: right"]4.00%[/TD]
[TD="class: xl65, width: 64, align: right"]5.00%[/TD]
[TD="class: xl65, width: 64, align: right"]6.00%[/TD]
[/TR]
[TR]
[TD="class: xl68"]13[/TD]
[TD="class: xl65, width: 64, align: right"]0.00%[/TD]
[TD="class: xl70, width: 64, align: right"]0.00%[/TD]
[TD="class: xl70, width: 64, align: right"]0.00%[/TD]
[TD="class: xl69, width: 64, align: right"]3.00%[/TD]
[TD="class: xl65, width: 64, align: right"]4.00%[/TD]
[TD="class: xl65, width: 64, align: right"]5.00%[/TD]
[TD="class: xl65, width: 64, align: right"]6.00%[/TD]
[/TR]
[TR]
[TD="class: xl68"]14[/TD]
[TD="class: xl65, width: 64, align: right"]0.00%[/TD]
[TD="class: xl70, width: 64, align: right"]0.00%[/TD]
[TD="class: xl69, width: 64, align: right"]2.00%[/TD]
[TD="class: xl70, width: 64, align: right"]3.00%[/TD]
[TD="class: xl65, width: 64, align: right"]4.00%[/TD]
[TD="class: xl65, width: 64, align: right"]5.00%[/TD]
[TD="class: xl65, width: 64, align: right"]6.00%[/TD]
[/TR]
[TR]
[TD="class: xl68"]15[/TD]
[TD="class: xl65, width: 64, align: right"]0.00%[/TD]
[TD="class: xl69, width: 64, align: right"]1.00%[/TD]
[TD="class: xl65, width: 64, align: right"]2.00%[/TD]
[TD="class: xl65, width: 64, align: right"]3.00%[/TD]
[TD="class: xl65, width: 64, align: right"]4.00%[/TD]
[TD="class: xl65, width: 64, align: right"]5.00%[/TD]
[TD="class: xl65, width: 64, align: right"]6.00%[/TD]
[/TR]
[TR]
[TD="class: xl68"]16[/TD]
[TD="class: xl65, width: 64, align: right"]0.00%[/TD]
[TD="class: xl65, width: 64, align: right"]1.00%[/TD]
[TD="class: xl65, width: 64, align: right"]2.00%[/TD]
[TD="class: xl65, width: 64, align: right"]3.00%[/TD]
[TD="class: xl65, width: 64, align: right"]4.00%[/TD]
[TD="class: xl65, width: 64, align: right"]5.00%[/TD]
[TD="class: xl65, width: 64, align: right"]6.00%[/TD]
[/TR]
[TR]
[TD="class: xl68"]17[/TD]
[TD="class: xl65, width: 64, align: right"]0.00%[/TD]
[TD="class: xl65, width: 64, align: right"]1.00%[/TD]
[TD="class: xl65, width: 64, align: right"]0.00%[/TD]
[TD="class: xl65, width: 64, align: right"]3.00%[/TD]
[TD="class: xl65, width: 64, align: right"]4.00%[/TD]
[TD="class: xl65, width: 64, align: right"]5.00%[/TD]
[TD="class: xl65, width: 64, align: right"]0.00%[/TD]
[/TR]
[TR]
[TD="class: xl68"]18[/TD]
[TD="class: xl65, width: 64, align: right"]1.00%[/TD]
[TD="class: xl65, width: 64, align: right"]0.00%[/TD]
[TD="class: xl65, width: 64, align: right"]0.00%[/TD]
[TD="class: xl65, width: 64, align: right"]3.00%[/TD]
[TD="class: xl65, width: 64, align: right"]4.00%[/TD]
[TD="class: xl65, width: 64, align: right"]0.00%[/TD]
[TD="class: xl65, width: 64, align: right"]0.00%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl66"]AFTER[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl67"]A[/TD]
[TD="class: xl67"]B[/TD]
[TD="class: xl67"]C[/TD]
[TD="class: xl67"]D[/TD]
[TD="class: xl67"]E[/TD]
[TD="class: xl67"]F[/TD]
[TD="class: xl67"]G[/TD]
[/TR]
[TR]
[TD="class: xl68"]11[/TD]
[TD="class: xl65, width: 64, align: right"]0.00%[/TD]
[TD="class: xl65, width: 64, align: right"]0.00%[/TD]
[TD="class: xl65, width: 64, align: right"]0.00%[/TD]
[TD="class: xl65, width: 64, align: right"]0.00%[/TD]
[TD="class: xl65, width: 64, align: right"]0.00%[/TD]
[TD="class: xl69, width: 64, align: right"]5.00%[/TD]
[TD="class: xl65, width: 64, align: right"]6.00%[/TD]
[/TR]
[TR]
[TD="class: xl68"]12[/TD]
[TD="class: xl65, width: 64, align: right"]0.00%[/TD]
[TD="class: xl70, width: 64, align: right"]0.00%[/TD]
[TD="class: xl70, width: 64, align: right"]0.00%[/TD]
[TD="class: xl70, width: 64, align: right"]0.00%[/TD]
[TD="class: xl69, width: 64, align: right"]4.00%[/TD]
[TD="class: xl65, width: 64, align: right"]5.00%[/TD]
[TD="class: xl65, width: 64, align: right"]6.00%[/TD]
[/TR]
[TR]
[TD="class: xl68"]13[/TD]
[TD="class: xl65, width: 64, align: right"]0.00%[/TD]
[TD="class: xl70, width: 64, align: right"]0.00%[/TD]
[TD="class: xl70, width: 64, align: right"]0.00%[/TD]
[TD="class: xl69, width: 64, align: right"]3.00%[/TD]
[TD="class: xl65, width: 64, align: right"]4.00%[/TD]
[TD="class: xl65, width: 64, align: right"]5.00%[/TD]
[TD="class: xl65, width: 64, align: right"]6.00%[/TD]
[/TR]
[TR]
[TD="class: xl68"]14[/TD]
[TD="class: xl65, width: 64, align: right"]0.00%[/TD]
[TD="class: xl70, width: 64, align: right"]0.00%[/TD]
[TD="class: xl69, width: 64, align: right"]2.00%[/TD]
[TD="class: xl70, width: 64, align: right"]3.00%[/TD]
[TD="class: xl65, width: 64, align: right"]4.00%[/TD]
[TD="class: xl65, width: 64, align: right"]5.00%[/TD]
[TD="class: xl65, width: 64, align: right"]6.00%[/TD]
[/TR]
[TR]
[TD="class: xl68"]15[/TD]
[TD="class: xl65, width: 64, align: right"]0.00%[/TD]
[TD="class: xl69, width: 64, align: right"]1.00%[/TD]
[TD="class: xl65, width: 64, align: right"]2.00%[/TD]
[TD="class: xl65, width: 64, align: right"]3.00%[/TD]
[TD="class: xl65, width: 64, align: right"]4.00%[/TD]
[TD="class: xl65, width: 64, align: right"]5.00%[/TD]
[TD="class: xl65, width: 64, align: right"]6.00%[/TD]
[/TR]
[TR]
[TD="class: xl68"]16[/TD]
[TD="class: xl65, width: 64, align: right"]0.00%[/TD]
[TD="class: xl65, width: 64, align: right"]1.00%[/TD]
[TD="class: xl65, width: 64, align: right"]2.00%[/TD]
[TD="class: xl65, width: 64, align: right"]3.00%[/TD]
[TD="class: xl65, width: 64, align: right"]4.00%[/TD]
[TD="class: xl65, width: 64, align: right"]5.00%[/TD]
[TD="class: xl65, width: 64, align: right"]6.00%[/TD]
[/TR]
[TR]
[TD="class: xl68"]17[/TD]
[TD="class: xl65, width: 64, align: right"]0.00%[/TD]
[TD="class: xl65, width: 64, align: right"]1.00%[/TD]
[TD="class: xl65, width: 64, align: right"]2.00%[/TD]
[TD="class: xl65, width: 64, align: right"]3.00%[/TD]
[TD="class: xl65, width: 64, align: right"]4.00%[/TD]
[TD="class: xl65, width: 64, align: right"]5.00%[/TD]
[TD="class: xl65, width: 64, align: right"]0.00%[/TD]
[/TR]
[TR]
[TD="class: xl68"]18[/TD]
[TD="class: xl65, width: 64, align: right"]1.00%[/TD]
[TD="class: xl65, width: 64, align: right"]1.00%[/TD]
[TD="class: xl65, width: 64, align: right"]2.00%[/TD]
[TD="class: xl65, width: 64, align: right"]3.00%[/TD]
[TD="class: xl65, width: 64, align: right"]4.00%[/TD]
[TD="class: xl65, width: 64, align: right"]5.00%[/TD]
[TD="class: xl65, width: 64, align: right"]0.00%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Ok, try this
Code: 
   Range(activecell.Offset(1, 0), activecell.End(xlDown)).Value = activecell.Value
   activecell.Offset(-1, 1).Select
   Range(activecell.Offset(1, 0), activecell.End(xlDown)).Value = activecell.Value
   activecell.Offset(-1, 1).Select
   Range(activecell.Offset(1, 0), activecell.End(xlDown)).Value = activecell.Value
 
Upvote 0
Fluff said:
Ok, try this
Code: 
   Range(activecell.Offset(1, 0), activecell.End(xlDown)).Value = activecell.Value
   activecell.Offset(-1, 1).Select
   Range(activecell.Offset(1, 0), activecell.End(xlDown)).Value = activecell.Value
   activecell.Offset(-1, 1).Select
   Range(activecell.Offset(1, 0), activecell.End(xlDown)).Value = activecell.Value
Click to expand...


Thanks. That works!
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0
You must log in or register to reply here.

Similar threads

willow1985
  • Solved
VBA Run-time error 91 - Object variable or With block variable not set when trying to refresh data/Sleep
Replies
4
Views
400
willow1985
willow1985
S
  • Question Question
Dynamic Address by VBA
Replies
8
Views
212
MikeVol
MikeVol
C
  • Question Question
Please help clean up my convoluted VBA code. It seems very redundant and repetitive.
Replies
1
Views
441
myall_blues
myall_blues
K
  • Question Question
Increasing row before the macro runs again
Replies
1
Views
230
kbk
K
D
  • Solved
extract to another workbook and save using desktop path
2
Replies
12
Views
209
jolivanes
jolivanes

Forum statistics

Threads
1,224,823
Messages
6,181,173
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top