VBA Problem (easy for you)

Jay Baker

New Member
Joined
Dec 9, 2003
Messages
15
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 :unsure:

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,
:oops: :oops: :oops: Jay
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,239
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
Hi Jay,
Give this a try and see if it works any better.
Code:
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

New Member
Joined
Dec 9, 2003
Messages
15
Hi Tom and Dan,

Thanks for all your help,

Unfortunately, I keep getting the runtime error :oops:

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

Maybe I am not typing something??
Jay
 

Jay Baker

New Member
Joined
Dec 9, 2003
Messages
15

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,239
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

Well-known Member
Joined
Jul 14, 2003
Messages
1,017

ADVERTISEMENT

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

New Member
Joined
Dec 9, 2003
Messages
15
OK, here's the situation,

I closed all my programs, restarted the computer, created a new workbook, (took a ****), :LOL: 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! :devilish:

Do they still sell Lotus 123?? o_O

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

:oops: :oops: :oops:
Jay
 

Chitosunday

Well-known Member
Joined
Jul 14, 2003
Messages
1,017
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

Board Regular
Joined
May 20, 2003
Messages
114
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,112
Messages
5,640,173
Members
417,129
Latest member
geekzilla

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top