Newbie error 1004 Application-defined or object-defined error

[jvlavl]

Hello,


i'm completely new to VBA


I receive the following error 1004 Application-defined or object-defined error.


the following code is processing this error.


What needs to be accomplished is that assumes that he is 100% minus the value in the previous cell is value in cell


a b c d
50 36 73% =100%-c1=d%




Can somebody help me here
This is the code I have


Sub Drivereport()


Dim Cn As ADODB.Connection
Dim Server_Name As String
Dim Database_Name As String
Dim User_ID As String
Dim Password As String
Dim SQLStr As String
Dim rs As ADODB.Recordset
Dim r As Long
Dim ws As Worksheet
Set rs = New ADODB.Recordset


With ActiveSheet


lastcol = .Cells(1, .Columns.Count).End(xlToLeft).Column
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row


For r = 2 To lastRow


x = Cells(r, 1) & Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Cells(r, 2), "free", ""), "total", ""), "used", ""), "vfs.fs.size[", ""), "]", ""), ":,", ""), "/,", ""), ",", "")
y = Cells(r + 1, 1) & Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Cells(r + 1, 2), "free", ""), "total", ""), "used", ""), "vfs.fs.size[", ""), "]", ""), ":,", ""), "/,", ""), ",", "")
Z = Cells(r + 2, 1) & Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Cells(r + 2, 2), "free", ""), "total", ""), "used", ""), "vfs.fs.size[", ""), "]", ""), ":,", ""), "/,", ""), ",", "")


Cells(1, lastcol + 1) = "Free"
Cells(1, lastcol + 2) = "Total"
Cells(1, lastcol + 3) = "Used"


If x = y And y = Z Then
Cells(r, lastcol + 1) = Cells(r, lastcol - 3)
Cells(r, lastcol - 3) = ""
Cells(r, lastcol + 2) = Cells(r + 1, lastcol - 4)
Cells(r + 1, lastcol - 4) = ""
Cells(r, lastcol + 3) = Cells(r + 2, lastcol - 2)
Cells(r + 2, lastcol - 2) = ""
ElseIf x = y Then
Cells(r, lastcol - 2) = Cells(r, lastcol - 4)
Cells(r, lastcol - 4) = ""
Cells(r, lastcol - 1) = Cells(r + 1, lastcol - 3)
Cells(r + 1, lastcol - 3) = ""


End If


Next


lastcol = .Cells(1, .Columns.Count).End(xlToLeft).Column
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row


For r = lastRow To 2 Step -1
If .Cells(r, lastcol).Value = 0 Or .Cells(r, lastcol).Value = "" Then
.Cells(r, lastcol).EntireRow.Delete
Else


.Cells(r, lastcol).FormulaR1C1 = "=CONVERT(" & .Cells(r, lastcol) & ",""byte"",""Gibyte"")"
.Cells(r, lastcol) = .Cells(r, lastcol)
.Cells(r, lastcol - 1).FormulaR1C1 = "=CONVERT(" & .Cells(r, lastcol - 1) & ",""byte"",""Gibyte"")"
.Cells(r, lastcol - 1) = .Cells(r, lastcol - 1)


End If


Next


Columns(lastcol).NumberFormat = "0.00"
Columns(lastcol - 1).NumberFormat = "0.00"


Cells(1, lastcol + 1) = "Used GB %"
Columns(lastcol + 1).NumberFormat = "0%"


lastcol = .Cells(1, .Columns.Count).End(xlToLeft).Column
lastRow = .Cells(Rows.Count, "A").End(xlUp).Row


For r = 2 To lastRow


Range(.Cells(r, lastcol), .Cells(lastRow, lastcol)).Formula = "=L2/K2"


Next


Cells(1, lastcol + 1) = "Free GB %"
Columns(lastcol + 1).NumberFormat = "0%"
lastcol = .Cells(1, .Columns.Count).End(xlToLeft).Column
lastRow = .Cells(Rows.Count, "A").End(xlUp).Row
For r = 2 To lastRow
'Range(.Cells(r, lastcol), .Cells(lastRow, lastcol)).Formula = "=100%-M2"
'Range(.Cells(r, lastcol + 1), .Cells(lastRow, lastcol + 1)).Formula = "=100%-(" & .Cells(r, lastcol) = .Cells(r, lastcol)
Range(.Cells(r, lastcol), .Cells(lastRow, lastcol)).Formula = "=100%-(" & .Cells(r, lastcol - 1) & ")"
Next


Application.ScreenUpdating = True


End With


End Sub


Rgds,


John

 

[steve the fish]

See your other thread. One thread is plenty for the same question!