VBA Problem (easy for you)

[Jay Baker]

I'm trying to select the first empty cell in a column (say column A) so I can paste some data.....the funny thing is, in my long subroutine the following code works, at times

But at other places, I keep getting Run-time error 1004 Application defined or Object defined error.

code:
Worksheets("INPUT").Select
Range("A2").Select
Selection.Copy
Worksheets("SALES").Select
Range("A1").End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste

Thanks,
Frustrated,
Jay

 

[Tom Urtis]

I then entered the following:

Sub Macro1()
Sheets("Sheet1").Select
Range("A2").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A1").End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub

Guess what....my good friend Runtime error 1004 reared his ugly head again!

This is my final attempt on this thread.

You will get a 1004 error if there is nothing in column A of the Sales sheet to begin with if you use that code of yours.

You will not get an error if you use what I posted for Test2, unless the Sales sheet is protected or cells are merged or some other who-knows-what-else thing is going on.

 

[Jay Baker]

OhMyGOD Chitosunday, ray: ray: ray:

IT WORKS

I used what you provided and put it several places in what I have written.
My little subroutine is getting so long I feel like a real "programmer"

Thanks again,
Jay

 

[HalfAce]

Hi Jay,
Looks like Chitosunday has your problem solved. Just a couple of points to think about. (1) If you avoid selecting in your code (similar to what I supplied) it will run much faster. (2) Fletch brings up a good point with starting at the bottom of Col A and working your way up instead of starting at the top & working down. (As long as there are no used cells in a range below whatever you're doing). This method will prevent the error if Col A is empty. (3) Tom provided a couple routines that work quite well that also check for error producing conditions and prevent them. You should (if you can) take the time to understand how these work. They will help you in the future. I find that when Tom (and Chitosunday for that matter!) "speak" it's usually beneficial to "listen".

Glad you got it working

Dan