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

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

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

Open in new window

I guess Log10 is wrong but how to correct it?
0
zhshqzyc
Asked:
zhshqzyc
1 Solution
 
Rory ArchibaldCommented:
What is in vardata(x, y) when the error occurs (assuming it is on that line)
0
 
zhshqzycAuthor Commented:
It is 0.663551743085175
0
 
zhshqzycAuthor Commented:
Hold on. Let me double check.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Rory ArchibaldCommented:
Are you positive? That should not cause an error. Is it definitely an error on that line?
0
 
kgerbChief 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
 
NorieCommented:
Log10 is fine.

What values do you have in the range E2:Z70?
0
 
zhshqzycAuthor Commented:
There is a zero.
0
 
zhshqzycAuthor 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

Open in new window

The fouth row didn't change at all.
0
 
zhshqzycAuthor Commented:
My fault:
RANGE WRONG.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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