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]

This will cover all your bases.

From the bottom of Sales, looking up to the next cell in column A:

Sub Test1()
Sheets("INPUT").Range("A2").Copy Sheets("SALES").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
End Sub



From the top of column A of Sales, looking down, considering that A1 or A2 might be empty:

Sub Test2()
Dim x As Range
Set x = Sheets("SALES").Range("A1")
With Sheets("INPUT").Range("A2")
If IsEmpty(x) Then
.Copy x
ElseIf IsEmpty(x.Offset(1, 0)) Then
.Copy x.Offset(1, 0)
Else
.Copy x.End(xlDown).Offset(1, 0)
End If
End With
End Sub

 

[HalfAce]

Hi Jay,
Give this a try and see if it works any better.

Sheets("INPUT").Range("A2").Copy Sheets("SALES").Range("A1").End(xlDown).Offset(1)

Hope this helps,
Dan

Edit: (Looks like Tom got in a good one before I got here.)

 

[Jay Baker]

Hi Tom and Dan,

Thanks for all your help,

Unfortunately, I keep getting the runtime error

I have copied and pasted both codes and they both produce the same error.

Maybe I am not typing something??
Jay

 

[Jay Baker]

Tom,

Does the Sub Test2() allow me to copy a value from SALES starting at A1 if it is empty? If I want to copy a value from INPUT A2 to an empty cell in SALES column A, would I need to change all the COPY s to PASTE s ?

Thanks,
Jay

 

[Tom Urtis]

First, the code from me and from HalfAce does not error. They are both good macros.

Second, the answer is Yes to all your questions.

Third, your 1004 error has nothing to do with code. I bet it is either because your Sales sheet is protected, or you have some cells merged in column A, or something else you are not telling us.

Take a close look at your workbook, especially the Sales sheet, and the mystery will be solved right there somewhere when you look carefully at how your workbook is really set up.

 

[Chitosunday]

I think the code
Sheets("INPUT").Range("A2").Copy Sheets("SALES").Range("A1").End(xlDown).Offset(1)
can produce an error if the column a of sales is completely empty because it will go to row 65536 which is the maximum row and you instruct the computer to go to 65537 when you type offset(1). The code should have this:

On Error Resume Next
Sheets("input").Range("a2").Copy Sheets("SALES").Range("A1").End(xlDown).Offset(1)
If Err.Number > 0 Then
Err.Clear
If IsEmpty(Sheets("Sales").Range("a1")) Then
Sheets("input").Range("a2").Copy Sheets("sales").Range("a1")
Else
Sheets("input").Range("a2").Copy Sheets("sales").Range("a2")
End If
End If

 

[Jay Baker]

OK, here's the situation,

I closed all my programs, restarted the computer, created a new workbook, (took a ****), and put some numbers in column A on sheet1 and sheet2.

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!

Do they still sell Lotus 123??

BTW, Chitosunday, I added that part to the code and guess what...


Jay

 

[Chitosunday]

Is your code like this:
Sub Macro1()
Sheets("sheet1").Select
On Error Resume Next
Range("a2").Copy Sheets("sheet2").Range("A1").End(xlDown).Offset(1)
If Err.Number > 0 Then
Err.Clear
If IsEmpty(Sheets("sheet2").Range("a1")) Then
Range("a2").Copy Sheets("sheet2").Range("a1")
Else
Range("a2").Copy Sheets("sheet2").Range("a2")
End If
End If
End Sub

 

[Fletchmeister]

Hi don't know if this will help but i've always found this useful

Range("a1")
Range("A65536").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select

Just change the initial Range, in this case A1 to which column you want to select the blank line in.

Fletch