VBA Runtime Error 1004 “Application-defined or Object-defined error” when setting Range in macro

Question

I'm going to preface this by saying that I am very new to VBA and this is my first project with it however, I'm trying quite hard because otherwise it is manual copy paste ~200 times.

Unfortunately, for a first project it has been difficult.

EDITED FOR CLARITY (HOPEFULLY): The main idea is that I need to start at the beginning of a drop down list, copy the first string listed, then paste that string down the column. This changes the numerical data adjacent to the right. I then want to select this newly changed numerical data and copy and paste it to a different sheet in the same workbook in the first blank space in column F. I then want the code to iterate through the drop down list and do this for all 51 strings in the drop down. However it needs to paste offset by 3 columns for each iteration to copy the data to the correct column in the other sheet.

Here is my code thus far

Option Explicit

Sub PtComp()
'
' PtComp Macro
'

'
Dim List1 As String
Dim Range1 As Range
Dim Line1 As Range
Dim i As Integer
Dim Begin As Range


ActiveWorkbook.Sheets("Sample Data Summary").Activate
List1 = Selection
Set Range1 = Evaluate(ActiveSheet.Range(List1).Validation.Formula1)
For Each Line1 In Range1
    Selection.Copy
    ActiveSheet.Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 1).Select
    ActiveSheet.Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    ActiveSheet.Selection.Copy
    ActiveWorkbook.Sheets("Pt Comparison").Activate
    Begin = ActiveSheet.Range("F1").End(xlDown).Offset(-1, 0)
    For i = 0 To 148 Step 3
    Begin.Offset(0, i).Select
    ActiveSheet.PasteSpecial Paste:=xlPasteValues
    Next i
    Next Line1

End Sub

It is highlighting this line

Set Range1 = Evaluate(ActiveSheet.Range(List1).Validation.Formula1)

Any help would be greatly appreciated. Sorry if my code is trash, like I said, first timer hoping to get better.

EDIT: Also, I looked back at older questions with the same error and thought that it was maybe because it wasn't clear what worksheet I was trying to define the range in, hence why my code is full of 'ActiveSheet' but still no luck.

What do you think you are getting with this line: `List1 = Selection`? — Scott Craner, Jul 12 '17 at 18:01
Use `msgbox List1` after your `List1 = Selection` line. As a beginner, `msgbox` is your friend... you can print to the screen while the program is running. Also, learn to use the debugger... it'll let you know what your variables are doing and how your logic is flowing. I'd suspect that List1 doesn't have a proper range in it. — abraxascarab, Jul 12 '17 at 18:04
One step beyond `msgbox` are the `Immediate`, `Locals` and `Watch` windows. They are vital to efficient debugging. — Brandon Barney, Jul 12 '17 at 18:35
Prefer `Debug.Print` and `Debug.Assert` calls over sprinkled `MsgBox` calls... — Mathieu Guindon, Jul 12 '17 at 19:26
@ScottCraner I was defining List1 as the current selection when starting the sub. Because I want it to start at different positions. This workbook has a ton of data that I'm still adding to and after each addition I have to manually change the drop down list and copy paste to another sheet. After running and hitting debug and highlighting List1 it is showing the value in the selected cell which also shows if i put `msgbox List1` after that line. — gbs6146, Jul 12 '17 at 19:34

score 0 · Answer 1 · edited Sep 21 '17 at 18:02

0

Your code assumes List1 contains a valid range address, and thus that the active cell on that "Sample Data Summary" worksheet, contains a valid range address.

Apparently that isn't always the case. Search for more details on the On Error statement for ideas about how you could go about handling that situation.

You need to read up on How to avoid using Select in Excel VBA macros, and know that clipboard operations in a tight loop is pretty much the single slowest thing you can do in Excel-VBA.

Seems you want something like this:

Set Range1 = Evaluate(Selection.Validation.Formula1)

Your code blows up on Range(List1) because List1 does not contain a valid range address.

edited Sep 21 '17 at 18:02

Graham

7,431
18
59
84

answered Jul 12 '17 at 19:32

Mathieu Guindon

69,817
8
107
235

So my confusion here is like I said that this needs to start at different positions. Even if define `List1 = Range("C168")` (the currently selected cell), I'm getting the same error. EDIT: Is it possible I'm running into an error because I want the range to be the values contained in the drop down list in C168? How would I go about defining that? – gbs6146 Jul 12 '17 at 19:47
`List1 = Range("C168")` and then `Evaluate(ActiveSheet.Range(List1)` assumes `Range("C168")` contains a valid range address. What is `List1` supposed to be anyway? – Mathieu Guindon Jul 12 '17 at 19:54
`List1` is just the variable that I set trying to contain the values of the drop down list. Pretty much each cell in C has this drop down list of strings that then change the values in the adjacent cells in D. I was pretty much trying to copy [this post](https://stackoverflow.com/questions/36398323/iterate-through-vba-dropdown-list) of iterating through a drop down list. – gbs6146 Jul 12 '17 at 20:00
@gbs6146 to be clear: **your code wants List1 to contain a valid range address**, not a list of things, not strings, not anything but a range address (or named range). – Mathieu Guindon Jul 12 '17 at 20:01
OK, how can I fix that, or what should I be trying to make it to get it to recognize the other strings within the drop down? I need it to iterate through each string in the list and then copy the data in the adjacent column? I'm sorry, like I said, I'm extremely new to VBA. – gbs6146 Jul 12 '17 at 20:07
@gbs6146 edited, see if that works for you - I'm not 100% sure exactly what you're trying to do, there's too much `Select` and `Activate` and `Copy` / `Paste` going on to clearly visualize what that code is supposed to be doing / working with. – Mathieu Guindon Jul 12 '17 at 20:13
Same error. I know there's a lot going on, I tried to explain myself in writing at the beginning of the OP. I just edited it again for more clarity hopefully. I was trying to write a code that I could start at the beginning of each addition of new data so that it didn't have to copy paste the entire sheet each time but it's looking like that's causing problems. (p.s. thank you for trying to help I know it's like pulling teeth since I have little idea what I'm doing) – gbs6146 Jul 12 '17 at 20:26

VBA Runtime Error 1004 “Application-defined or Object-defined error” when setting Range in macro

1 Answers1