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, Apr 05 '16 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, Apr 05 '16 at 14:26
for your information, no cells are in active in your code to select the entire column — Karthick Gunasekaran, Apr 05 '16 at 14:28
would the active cell not be the cell its currently looping through? sorry if is a daft question @karthick — HushHoney, Apr 05 '16 at 14:35
yes, range has no capital R which is strange, ill try that @PaulOgilvie thanks — HushHoney, Apr 05 '16 at 14:37
@MacroMan ill try explain as best as I can.. i want to loop through the A row and i want to skip the cell is there is no value, but when there is a value i want to copy the values in the corresponding column and save them to another worksheet to the range stated in the active cells value. sorry if that doesn't make sense. — HushHoney, Apr 05 '16 at 14:38
Question for those saying the "R" isn't capitalized - VBA should capitalize it automatically, if it reads it correctly as a `Range`. Since it's not capitalizing, I don't know if it's reading the `Range` correctly... — BruceWayne, Apr 05 '16 at 14:55
@BruceWayne the code is working now, however range is still not capitalized — HushHoney, Apr 05 '16 at 14:58

score 1 · Answer 1 · edited Apr 05 '16 at 14:38

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!

score 0 · Accepted Answer · answered Apr 05 '16 at 14:34

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 05 '16 at 14:34

Karthick Gunasekaran

2,697
1
15
25

you were right there was no active cel, ive replaced acticecell with `Cells(1, lCol).EntireColumn.Select` and its worked :) thanks – HushHoney Apr 05 '16 at 14:48
@HushHoney, please mark as this is solution for your question – Karthick Gunasekaran Apr 05 '16 at 14:51
@HushHoney - While this certainly works, it still leaves your macro using `.Select`, which isn't best practice. Read through [this page](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) as using `.Select` can cause many headaches, especially as you bounce between worksheets/workbooks, which was some of the problem here. – BruceWayne Apr 05 '16 at 14:59
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 Apr 05 '16 at 15:02
1

@BruceWayne thanks, will look into it, you all have been so helpful – HushHoney Apr 05 '16 at 15:02

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

2 Answers2