excel-vba Range.Offset Error

Question

I have an issue with the following VBA code:

Set timeRange = ActiveSheet.Cells.Find("DATE", MatchCase:=True) 'Cerca TIME column
 If timeRange Is Nothing Or Empty Then GoTo errorOpenColumn
 timcol = timeRange.Column
 Columns(timcol).Select
 Selection.NumberFormat = "yyyymmdd"
 Selection.Copy
 'Workbooks(Mainbook).Activate
 'Worksheets("Office_Data_Table").Select
 Workbooks(Mainbook).Activate
 Worksheets("Office_Data_Table").Activate
 Range("A:A").Offset(6, 0).PasteSpecial 'Error aqui
 Windows(Sourcebook).Activate

At the time it executes it will give an error in line Range("A:A").Offset(6, 0).PasteSpecial, but would not give an error (No valid entry. Application will be closed); but if I apply the Offset(0,6) it will work.

Obviously my problem is that I want to apply the Offset (6,0), and not the other one :-).

Any help highly appreciated.

INTERESTING READ

Siddharth Rout
– Siddharth Rout

2013-11-21 13:20:35 +00:00
Commented Nov 21, 2013 at 13:20 — Siddharth Rout
– Siddharth Rout, Commented Nov 21, 2013 at 13:20

Peter Albert · Accepted Answer · 2013-11-21 13:19:08Z

2

Range("A:A") selects the whole column - and OFFSET(6,0) tries to offset the whole column down - which is not possible as it's already completely selected! Instead, you might want to use Range("A1"). Offset(6,0) or Range("A7") directly.

answered Nov 21, 2013 at 13:19

Peter Albert

17.6k6 gold badges68 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

excel-vba Range.Offset Error

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related