PDA

View Full Version : [SOLVED] Run-time Error 1004



Durmir
07-27-2015, 06:38 AM
Hi all,

I am trying to get a macro to work on Excel. I have 2 sheets. On the first one is my data (13 columns), and my second one is my "results" sheet.
I want to filter my data on my 13th column (M) and get the sum of the filtered items for 4 different columns (I, J, K, L), then get the results pasted on my results sheet (resp. columns B, C, D, E).
I am very new to VBA and tried to put together what I could, as follows:


Function sumrange(rng As Range)

summ = 0
For Each Cell In rng
summ = summ + Cell.Value
Next
sumrange = summ

End Function



Private Sub CommandButton1_Click()

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

Dim LastRow, LastRow2 As Integer
Dim Assets As Worksheet
Dim Results As Worksheet
Dim t, u, v, w, y, z, k
Dim DestRow As Long

Set Assets = Sheets("Assets 1995+")
Set Results = Sheets("Results")
LastRow = Assets.Cells(Rows.Count, 1).End(xlUp).Row
LastRow2 = Results.Cells(Rows.Count, 1).End(xlUp).Row

Results.Range("I2:L" & LastRow2).ClearContents

For z = 1 To 70
With Assets.Range("M3", "M" & LastRow)
Set y = .Find(z, LookIn:=xlValues)
If Not y Is Nothing Then
k = z + 1
With Assets.Range("A1", "M" & LastRow)
.AutoFilter
.AutoFilter Field:=13, Criteria1:=z
DestRow = Results.Cells(Rows.Count, "L").End(xlUp).Row + 1
Range(Assets.Range("I3"), Assets.Range("M3").End(xlDown)).Copy ***
Results.Range("I2").PasteSpecial xlPasteValues
DestRow = Results.Cells(Rows.Count, "L").End(xlUp).Row + 1
t = sumrange(Results.Range("I2:I" & DestRow))
u = sumrange(Results.Range("J2:J" & DestRow))
v = sumrange(Results.Range("K2:K" & DestRow))
w = sumrange(Results.Range("L2:L" & DestRow))
Results.Range("B" & k).Value = t
Results.Range("C" & k).Value = u
Results.Range("D" & k).Value = v
Results.Range("E" & k).Value = w
Results.Range("I2:L" & LastRow2).ClearContents
.AutoFilter
t = 0
u = 0
v = 0
w = 0
End With
End If
k = 0
End With
Next z

Results.Range("I2:L" & LastRow2).ClearContents

Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic

End Sub

Unfortunately, I keep getting a "Run-time error: 1004. Method 'Range' of object '_Worksheet' failed". The debug brings me to the line I marked with ***.
I looked on the forum, found several topic about this error, tried all solutions. But it does not work...

If anyone has an idea, it'd be immensely appreciated :)

Thanks!

p45cal
07-27-2015, 07:19 AM
try changing:
Range(Assets.Range("I3"), Assets.Range("M3").End(xlDown)).Copy
to:
Assets.Range(Assets.Range("I3"), Assets.Range("M3").End(xlDown)).Copy

Durmir
07-27-2015, 07:27 AM
Thanks a lot, p46cal! It worked like a charm!