VBA Newbie

[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.

Cells(1, lastcol + 1) = "xxxxxx"
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

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

[TABLE="width: 500"]
<tbody>[TR]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d[/TD]
[/TR]
[TR]
[TD]50[/TD]
[TD]36[/TD]
[TD]73%[/TD]
[TD]=100%-c1=d%[/TD]
[/TR]
</tbody>[/TABLE]


Can somebody help me here

Rgds,

John

 

[steve the fish]

Hi. That isnt complete. What is the whole code?

 

[jvlavl]

Hello

This is almost the complete code I only removed the database connection.

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]

Ok. I think you need a formula like this:

Range(.Cells(2, lastcol + 1), .Cells(lastRow, lastcol + 1)).FormulaR1C1 = "=100%-RC[-1]"

Note that i have used 2 rather than r. This means this no longer needs to be placed in a loop so remove the For 2 to lastrow and the next.

Also you have lots of unqualified ranges in here. Sometimes you qualify (eg .Cells) and sometimes you dont (Cells). This could lead to unexpected results.

 

[jvlavl]

Steve,

Thank you

Rgds,

John