1004 error for vba script

Question

I have a code that I merged with another code. On their own both codes work fine, but now I encounter a 1004 runtime error and I cant figure out why It gives a 1004 error for the second to last line "Set = Varrange ..."

Sub Simulate500k()

Dim i As Integer
Dim p As Integer
Dim k As Integer
Dim ROI As Integer
Dim Nummer As Integer
Dim NBC As Integer
Dim Spalte As Integer
Dim n As Integer
Dim m As Integer
Dim nblock As Integer




Nummer = Worksheets("Control").Cells(10, 2).Value
ROI = Worksheets("Control").Cells(9, 3).Value
NBC = Worksheets("Control").Cells(10, 5).Value
Spalte = Worksheets("Control").Cells(7, 5).Value
n = Worksheets("Control").Cells(11, 5).Value
nblock = Worksheets("Control").Cells(12, 5).Value


For k = nblock To 260

Worksheets("table").Cells(ROI * 17 + 1, 4 + 12 * NBC).Copy
Worksheets("Control").Cells(15, 2).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Worksheets("table").Cells(ROI * 17 + 1, 6 + 12 * NBC).Copy
Worksheets("Control").Cells(6, 2).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

For m = 1 To 14

 For i = 4 To 103

  Worksheets("500k").Cells(5, 2).Copy
  Worksheets("500k").Cells(2, i).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

  Set varRangeselect1 = Worksheets("500k").Range(Cells(6, 2), Cells(106, 2))
  Set varRangeSelect2 = Worksheets("500k").Range(Cells(6, i), Cells(106, i))
  varRangeselect1.Copy
  varRangeSelect2.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

I don't see where you declare the varRangeSelect2 variable. Are you sure you declared it as a range variable? — nbayly
– nbayly, Commented Mar 4, 2016 at 21:37

Scott Craner · Accepted Answer · 2016-03-04 21:37:51Z

4

You need to assign parentage to all Range Objects including the Cells() inside the Range(). The easiest is with a With block:

With Worksheets("500k")
    Set varRangeselect1 = .Range(.Cells(6, 2), .Cells(106, 2))
    Set varRangeSelect2 = .Range(.Cells(6, i), .Cells(106, i))
End with

So inside the With block anything that starts with . is assigned to the parentage stated in the With statement.

answered Mar 4, 2016 at 21:37

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

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

1 Comment

Scott Craner Over a year ago

@IschaIschratioh I am glad. Please come back in a few minutes and mark it as correct.

Collectives™ on Stack Overflow

1004 error for vba script

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related