Solved

# Application-defined or object-defined error

Posted on 2011-10-17
168 Views
``````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
Question by:zhshqzyc

LVL 85

Expert Comment

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

Author Comment

It is 0.663551743085175
0

Author Comment

Hold on. Let me double check.
0

LVL 85

Expert Comment

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

LVL 12

Accepted Solution

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

LVL 33

Expert Comment

Log10 is fine.

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

Author Closing Comment

There is a zero.
0

Author Comment

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 Comment

My fault:
RANGE WRONG.
0

## Featured Post

### Suggested Solutions

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.