Newbie error 1004 Application-defined or object-defined error

jvlavl

New Member
Joined
Nov 27, 2017
Messages
15
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
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,403
Office Version
  1. 365
Platform
  1. Windows
See your other thread. One thread is plenty for the same question!
 

Watch MrExcel Video

Forum statistics

Threads
1,130,307
Messages
5,641,440
Members
417,209
Latest member
Agbarker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top