[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 178

Application-defined or object-defined error

``````Option Explicit

Sub Convert()
Dim r1                As Range
Dim varData
Dim x As Long, y As Long
Application.ScreenUpdating = False
Set r1 = Sheet1.Range("E2", "Z70")
varData = r1.Value2
For x = 1 To UBound(varData, 1)
For y = 1 To UBound(varData, 2)
If IsNumeric(varData(x, y)) And x Mod 3 <> 0 Then
varData(x, y) = -1 * Application.WorksheetFunction.Log10(varData(x, y))

End If
Next y
Next x
r1.Value2 = varData
Application.ScreenUpdating = True
End Sub
``````
I guess Log10 is wrong but how to correct it?
0
zhshqzyc
1 Solution

Commented:
What is in vardata(x, y) when the error occurs (assuming it is on that line)
0

Author Commented:
It is 0.663551743085175
0

Author Commented:
Hold on. Let me double check.
0

Commented:
Are you positive? That should not cause an error. Is it definitely an error on that line?
0

Chief EngineerCommented:
To go along with Rory's point:

LOG10(number)
-If "number" < 0, then #NUM! is returned.
-If "number" is not an integer, it is truncated.
-If "number" is not numeric, then #VALUE! is returned.
0

Commented:
Log10 is fine.

What values do you have in the range E2:Z70?
0

Author Commented:
There is a zero.
0

Author Commented:
One following up question, why only the first three rows were updated?
``````Sub Convert()
Dim r1                As Range
Dim varData
Dim x As Long, y As Long
Application.ScreenUpdating = False
Set r1 = Sheet1.Range("E2", "Z70")
varData = r1.Value2
For x = 1 To UBound(varData, 1)
For y = 1 To UBound(varData, 2)
If IsNumeric(varData(x, y)) And varData(x, y) > 0 And x Mod 3 <> 0 Then
varData(x, y) = -1 * Application.WorksheetFunction.Log10(varData(x, y))

End If
Next y
Next x
r1.Value2 = varData
Application.ScreenUpdating = True
End Sub
``````
The fouth row didn't change at all.
0

Author Commented:
My fault:
RANGE WRONG.
0

Featured Post

Tackle projects and never again get stuck behind a technical roadblock.