2

I am getting an object error on the following code

lc = Cells(11, Columns.Count).End(xlToLeft).Column
Range(Cells(11, lc - 6), Cells(70, lc)).Cut
Range(Cells(11, lc - 4)).Select
ActiveSheet.Paste

Please advise

braX
  • 11,506
  • 5
  • 20
  • 33
sachin
  • 63
  • 4
  • Well it looks like lc value could be one or two. This could lead to negative value of coulmn index in your Range Cut line. – 1001001 Jun 04 '18 at 10:27
  • Thanks. But I am facing the issue in the last two lines of the code. it is selecting the correct range but it is throwing an error while cutting and pasting. Please revert – sachin Jun 04 '18 at 10:31

2 Answers2

0

lc is probably negative. Write this to see:

lc = Cells(11, Columns.Count).End(xlToLeft).Column
MsgBox lc

To make your code work, you have to pass String in the Select range. Currently, you are passing a cell, and you need to add its address:

Sub TestMe()

    Dim lc As Long
    lc = 10
    Range(Cells(11, lc - 6), Cells(70, lc)).Cut
    Range(Cells(11, lc - 4).Address).Select
    ActiveSheet.Paste

End Sub

Last, but not least, Select and Activate are considered a bad practice. Once you feel more secure in your VBA knowledge, try to avoid them - How to avoid using Select in Excel VBA.

Vityata
  • 42,633
  • 8
  • 55
  • 100
0
lc = Cells(11, Columns.Count).End(xlToLeft).Column
Range(Cells(11, lc - 6), Cells(70, lc)).Cut(ActiveSheet.Cells(11, lc - 4))

After Cut - you declare destination of cut cells. Suppose to work.

1001001
  • 236
  • 1
  • 6