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

x
?
Solved

loop the range

Posted on 2011-10-12
11
Medium Priority
?
250 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:zhshqzyc
  • 7
  • 3
11 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
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

Open in new window

0
 
LVL 17

Expert Comment

by:andrewssd3
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

by:zhshqzyc
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

Open in new window

0
Independent Software Vendors: 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!

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36956707
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
 
LVL 85

Expert Comment

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

Author Comment

by:zhshqzyc
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

by:Rory Archibald
ID: 36956753
What are you expecting as the result for a negative value?
0
 
LVL 85

Expert Comment

by:Rory Archibald
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")
      .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
 

Author Comment

by:zhshqzyc
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

by:Rory Archibald
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

Open in new window

0
 
LVL 85

Accepted Solution

by:
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

Open in new window

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!

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 …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question