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

x
Solved

# loop the range

Posted on 2011-10-12
Medium Priority
250 Views
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
``````
0
Question by:zhshqzyc
• 7
• 3

LVL 85

Expert Comment

ID: 36956611
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

LVL 17

Expert Comment

ID: 36956648
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 Comment

ID: 36956676
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

LVL 85

Expert Comment

ID: 36956707
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

LVL 85

Expert Comment

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

Author Comment

ID: 36956746
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

LVL 85

Expert Comment

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

LVL 85

Expert Comment

ID: 36956776
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 Comment

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

LVL 85

Expert Comment

ID: 36956924
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

LVL 85

Accepted Solution

Rory Archibald earned 2000 total points
ID: 36956936
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a ā¦