MACRO VBA - "run time error '1004' application - defined or object defined error"

Question

Hi can anyone figure out why I am getting this message..

run time error '1004' Application-defined or object-defined error

Here is my code, the problem line seems to be:

range("A1").Select

here is the rest of the code:

Sub HorizontalLoop()
Dim lCol As Long

Sheets("output").Select

For lCol = 1 To 100
    Dim inputrange As String
        If Not IsEmpty(Cells(lCol).Value) Then
           inputrange = Cells(1, lCol).Value
           ActiveCell.EntireColumn.Select
           Selection.Copy
           Sheets("input").Select
           range("A1").Select
           ActiveSheet.Paste
           Sheets("output").Select

        End If

Next lCol
End Sub

Thank you in advance :)

I notice that in range("A1").Select range is not with a capital, meaning that VB did not parse it as the Range method. Possibly you have to prepend the object of which you want to get a range, e.g. ActiveSheet.Range("A1").Select. — Paul Ogilvie
– Paul Ogilvie, Commented Apr 5, 2016 at 14:19
At the very top (before Sub HorizontalLoop()), put Option Explicit to make sure your variables are okay. I think you have a variable called range somewhere. Can you post the rest of your code? — BruceWayne
– BruceWayne, Commented Apr 5, 2016 at 14:26
for your information, no cells are in active in your code to select the entire column — Karthick Gunasekaran
– Karthick Gunasekaran, Commented Apr 5, 2016 at 14:28
would the active cell not be the cell its currently looping through? sorry if is a daft question @karthick — HushHoney
– HushHoney, Commented Apr 5, 2016 at 14:35

teo van kot · Accepted Answer · 2016-04-05 14:38:06Z

1

You need to qualify the Range("A1") with the the worksheet name (as mentioned in Paul Ogilvie's comment.

But you don't need to switch back and forth between the worksheets to paste. After the "copy" you can just add the "Destination".

I did a cut down version of your code (just 2 columns) so you can see what I mean:

Sub HorizontalLoop2()

Dim lCol As Long
Dim inputrange As String
Dim wsO As Worksheet
Dim wsI As Worksheet

Set wsO = ThisWorkbook.Worksheets("output")
Set wsI = ThisWorkbook.Worksheets("input")

For lCol = 1 To 2

If Not IsEmpty(wsO.Cells(lCol).Value) Then
inputrange = wsO.Cells(lCol).Value

wsO.Columns(lCol).Copy _
Destination:=wsI.Cells(1, lCol)

End If

Next lCol

End Sub

This assumes of course that you want to paste to the same column number!

edited Apr 5, 2016 at 14:38

teo van kot

12.5k10 gold badges41 silver badges73 bronze badges

answered Apr 5, 2016 at 14:34

ShirleyM

362 bronze badges

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

Comments

Karthick Gunasekaran · Accepted Answer · 2016-04-05 14:34:52Z

0

Assuming that Cell A13 is Active cell in worksheet output, see the below code

worksheets("output").Range("A13").Activate
ActiveCell.EntireColumn.Copy Worksheets("input").Range("A1")

it will copy the entire column and paste it to the worksheet input

answered Apr 5, 2016 at 14:34

Karthick Gunasekaran

2,7231 gold badge17 silver badges26 bronze badges

5 Comments

HushHoney Over a year ago

you were right there was no active cel, ive replaced acticecell with Cells(1, lCol).EntireColumn.Select and its worked :) thanks

Karthick Gunasekaran Over a year ago

@HushHoney, please mark as this is solution for your question

BruceWayne Over a year ago

@HushHoney - While this certainly works, it still leaves your macro using .Select, which isn't best practice. Read through this page as using .Select can cause many headaches, especially as you bounce between worksheets/workbooks, which was some of the problem here.

HushHoney Over a year ago

I wonder If you can help me with something else, in my code I am selecting the entire column, how would I select all the column from row 4 and down?

HushHoney Over a year ago

@BruceWayne thanks, will look into it, you all have been so helpful

Collectives™ on Stack Overflow

MACRO VBA - "run time error '1004' application - defined or object defined error"

2 Answers 2

Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related