Nutty VBA-Excel problem

Factual Questions
1

I’m writing a VBA macro in Excel 2003 to copy data from one worksheet to another. The macro is hanging on the next to last line here (bolded):


Sheets("Input Form").Select
Range("C2").Select
Selection.Copy
Sheets("Database").Select
**Range("A1").End(xlDown).Offset(1, 0).Select**
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False

I’ve even tried simplifying it to Range(“A1”).Select and it still gives the same error, namely:

I copied and pasted this code from another worksheet, and I can open that worksheet and it works fine. The sheet names are fine, because I can comment out the Range(“A1”)… line and the following and it goes to the sheet just fine. For some reason, the code is acting as if that sheet doesn’t have a cell A1, which is just plain nuts.

I’m usually one of the folks helping out with Excel problems, so I’m hoping to cash a little of that karma in here. Thanks in advance!

2

I assume you are trying to add the value of C2 on the Input Form to the end of a list on the Database sheet.
Try editing as follows:

Range(“A1”).Offset(1, 0).End(xlDown).Select

3

You may have a problem with the use of an unqualified Range. You must think about where this code resides and its scope.

In VBA for Excel, an unqualified Range reference is assumed to be within the worksheet in which the code is contained, not what is selected.

What sheet module is containing your code?

A second issue with your code is that you are selecting everything you want to work with. It is not necessary to do that, although if you use the macro recorder that’s the kind of code it generates. You also don’t need a Copy operation. A cleaner way of writing your code would be this one statement:



Sheets("Input Form").Range("C2").Value = _
  Sheets("Database").Range("A1").End(xlDown).Offset(1, 0).Value

Also note that this code qualifies the ranges, so will work no matter where it resides.

Although I am not the world’s foremost expert on VBA I have taught a class in it so I would be happy to help if you continue to have problems. Feel free to email your file if my advice didn’t work.

4

Also, I wanted to comment on this as well. Is there any data in any cell in Column A of the Database sheet? If not, that would explain the error. If there is no data in Column A, xlDown will select the cell in the very last row. You are then attempting to reference a cell beneath that (Offset(1, 0)).

5

I think you hit the nail on the head. When I went back and looked, it appears that the code was in the “Sheet1” space rather than the “Modules” space, where I usually write. I got in a hurry, what can I say.

Your suggested code is sweet, though, and I plan to not bother fixing what I’ve got but just stealing that.

There is data in the list, but that was a good suggestion. The suggested edit, though, would have resulted (I’m pretty sure) in over-writing the last cell in the list every time. The order given would tell it to go to cell A1, move down one, then move to the last cell with data in it.

6

I got my code backwards :o

You want this:



Sheets("Database").Range("A1").End(xlDown).Offset(1, 0).Value = _
   Sheets("Input Form").Range("C2").Value
7

:smiley:

Yeah, I actually figure that out, I just didn’t want to mention it. It’s all good, though. Thanks again!