Excel - Selecting Range of Cells <.10 3

[AGlazer]

Hi everyone.

I'm trying to write a macro that will select all cells from a specified range where one specific cell in the is <.10, then move those cells elsewhere, and perform some functions on them. I'm comfortable writing the code to move the cells and perform the functions I need, but how can I set up a loop to only find and highlight those cells where one specificed cell is <.10.

Any help would be appreciated.

Thanks,

Aaron

 

[VBAjedi]

I'm not entirely clear from your post - is &quot;<.10&quot; the actual value in the cell? If so, you should be able to set up a simple little loop to process the cells in the range.

Dim c as range

For Each c in MyTargetRange
If c.Value = &quot;<.10&quot; Then
' insert code here to Highlight the cell,
' add it to array, or whatever
End if
Next c

VBAjedi

 

[bryanbayfield]

Turn the macro recorder on, then use the advanced filter to copy teh cells in question to your required destination.

Turn your macro recorder off then have a look at the code and see if you can cannibalize it to your needs.

 

[AGlazer]

Sorry, didn't make it clear.

I have a table that looks like this:

Name Coeff Sig
A .45 .35
B 1.2 .80
C .8 .05
D .9 .15
E .2 .01

I want to be able to select all three columns for the rows there the value in the Sig is <.10.

Thanks!

Aaron

 

[VBAjedi]

Well, you could tweak my code as follows:

Dim c as range

For Each c in MyTargetRange
   If c.Value < .1 Then
      ' insert code here to Highlight the cell, 
      ' add it to array, or whatever
   End if
Next c

But this sounds like it might be a great candidate for the advanced filter. You could choose the option to paste the results to a new range, and do all your operations on that range.

VBAjedi

 

[Zathras]

It does sound like a great candidate for using a filter. But if you insist on using code, here is one way:
[blue]

Option Explicit

Sub test()
  SelectRowsByThreshhold Range("A1:C6"), 0.1, 3
End Sub

Sub SelectRowsByThreshhold(ARange As Range, Threshhold As Double, TestColumn As Integer)
Dim c As Range
Dim r As Range
  ARange.Cells(1, 1).Select
  For Each c In ARange.Columns(1).Cells
    If IsNumeric(c.Cells(1, TestColumn)) Then
      If c.Cells(1, TestColumn).Value < Threshhold Then
        If r Is Nothing Then
          Set r = Intersect(ARange, c.EntireRow)
        Else
          Set r = Union(r, Intersect(ARange, c.EntireRow))
        End If
      End If
    End If
  Next c
  If Not r Is Nothing Then
    r.Select
    Set r = Nothing
  End If
End Sub

[/color]

 

[AGlazer]

Thanks for your help, everyone!