1

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.

Improve this question
5
  • 1
    What do you think you are getting with this line: List1 = Selection? Commented Jul 12, 2017 at 18:01
  • 1
    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. Commented Jul 12, 2017 at 18:04
  • One step beyond msgbox are the Immediate, Locals and Watch windows. They are vital to efficient debugging. Commented Jul 12, 2017 at 18:35
  • Prefer Debug.Print and Debug.Assert calls over sprinkled MsgBox calls... Commented Jul 12, 2017 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. Commented Jul 12, 2017 at 19:34

1 Answer 1

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

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?
List1 = Range("C168") and then Evaluate(ActiveSheet.Range(List1) assumes Range("C168") contains a valid range address. What is List1 supposed to be anyway?
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 of iterating through a drop down list.
@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).
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.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.