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

Open in new window

zhshqzycAsked:
Who is Participating?
 
Rory ArchibaldCommented:
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

Open in new window

0
 
Rory ArchibaldCommented:
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

Open in new window

0
 
andrewssd3Commented:
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
zhshqzycAuthor 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

Open in new window

0
 
Rory ArchibaldCommented:
Or you could use:
Sub Convert()
   Dim r1                As Range
   Application.ScreenUpdating = False
   With Sheet3.Range("E3", "AO113")
      .Value = .Worksheet.Evaluate("INDEX(IF(" & .Address & "<>""."",LOG10(" & .Address & ")," & .Address & "),0, 0)")
   End With
   Application.ScreenUpdating = True
End Sub

Open in new window

0
 
Rory ArchibaldCommented:
PS Replace Sheet3 with Sheet1 in that last code.
0
 
zhshqzycAuthor 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
 
Rory ArchibaldCommented:
What are you expecting as the result for a negative value?
0
 
Rory ArchibaldCommented:
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")
      .Value = .Worksheet.Evaluate("INDEX(IF(" & .Address & "<>""."",IF(MOD(ROW(" & .Address & ")-2,3)<>0,LOG10(" & .Address & ")," & .Address & ")," & .Address & "),0, 0)")
   End With
   Application.ScreenUpdating = True
End Sub

Open in new window

0
 
zhshqzycAuthor Commented:
Your code is very concise and but a little hard to understand . Can you write code loop rows and columns?
Many thanks.
0
 
Rory ArchibaldCommented:
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

Open in new window

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.

All Courses

From novice to tech pro — start learning today.