# loop the range

How to loop the range?
``````Sub Convert()
Dim r1 As Range
Set r1 = Sheet1.Range(E3, AO113)
' if the cell's value="." do nothing
'else reassign the value=log(10)(cell'svalue)
End Sub
``````
###### Who is Participating?

Commented:
Or, assuming no formulas, you can loop an array instead:
``````Sub Convert()
Dim r1                As Range
Dim varData
Dim x As Long, y As Long
Application.ScreenUpdating = False
Set r1 = Sheet1.Range("E3", "AO113")
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 Then varData(x, y) = Application.WorksheetFunction.Log10(varData(x, y))
Next y
Next x
r1.Value2 = varData
Application.ScreenUpdating = True
End Sub
``````
0

Commented:
You could do this, which only loops through the numeric cells:
``````Sub Convert()
Dim r1                As Range
Application.ScreenUpdating = False
For Each r1 In Sheet1.Range("E3", "AO113").SpecialCells(xlCellTypeConstants, xlNumbers)
If IsNumeric(r1.Value) Then r1.Value = Application.WorksheetFunction.Log10(r1.Value)
Next r1
Application.ScreenUpdating = True
End Sub
``````
0

Commented:
You could take a look at this similar question which was asked recently: http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27386129.html
0

Author Commented:
If it is negative value. Then how?
``````Option Explicit

Sub Convert()
Dim c As Range
For Each c In Worksheets("sheet1").Range("E3:AO113").SpecialCells(xlCellTypeConstants, xlNumbers)

If IsNumeric(c.Value) Then
c.Value = -Log(c.Value) / Log(10)
End If
Next
End Sub
``````
0

Commented:
Or you could use:
``````Sub Convert()
Dim r1                As Range
Application.ScreenUpdating = False
With Sheet3.Range("E3", "AO113")
End With
Application.ScreenUpdating = True
End Sub
``````
0

Commented:
PS Replace Sheet3 with Sheet1 in that last code.
0

Author Commented:
Ok. I just found that
the row 1 and 2 are always positive. don't do row 3
row 4 and 5 are always positive. don't do row 6
row 7 and 8 are always positive. don't do row 9.
etc.
Then how?
0

Commented:
What are you expecting as the result for a negative value?
0

Commented:
So your rows 3 and 4 should be processed, skip 5, then do rows 6 and 7 and so on? If so:
``````Sub Convert()
Dim r1                As Range
Application.ScreenUpdating = False
With Sheet3.Range("E3", "AO113")
End With
Application.ScreenUpdating = True
End Sub
``````
0

Author Commented:
Your code is very concise and but a little hard to understand . Can you write code loop rows and columns?
Many thanks.
0

Commented:
Here's a loop that simply tests for values greater than 0:
``````Sub Convert()
Dim r1                As Range
Application.ScreenUpdating = False
For Each r1 In Sheet1.Range("E3", "AO113").SpecialCells(xlCellTypeConstants, xlNumbers)
If IsNumeric(r1.Value) Then
If r1.value > 0 then r1.Value = Application.WorksheetFunction.Log10(r1.Value)
End if
Next r1
Application.ScreenUpdating = True
End Sub
``````
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.