Auto Select Range?

C

cwilson

Hey all,

I am trying to get my macro to select all data (and perform calculations) if
that data is included in a range of G/L numbers. I remember seeing something
similar to this on this site, but I can't seem to find it again.

Basically, I have G/L #s from 701000 to 999999. 701000 to 703104 are loan
accounts. I want the macro to choose all of the accounts between and
including 701000 and 703104. That way, if accounting adds G/L 702101, I
won't have to change the macro.

Thanks in advance for the help.
 
D

Don Guillett

How about a nice formula where a1 and a2 contain your numbers or use the
numbers.
=sumproduct((myrng>=a1)*(myrng<a2)) to count
to sum myrng2 based on the criteria
=sumproduct((myrng>=a1)*(myrng<a2)*myrng2)
 
C

crispbd

This sub will select cells that fall between MinVal and MaxVal and plac
into
a final range object, MyFinishedRange. Once you have this range, yo
could
perform calculations by using a simple for-next loop on that range.


Sub MultipleSelect()
Dim myrange As Object, cellz As Object
Dim myrange2 As Object
Dim myFinishedRange As Object
Dim rngConstants As Object, rngFormulas As Object
Dim MinVal, MaxVal
' set min / max values to select
MinVal = 701000
MaxVal = 703104
'///

On Error Resume Next
Set rngConstants = ActiveSheet.Cells.SpecialCells(xlConstants)
Set rngFormulas = ActiveSheet.Cells.SpecialCells(xlFormulas)
For Each cellz In rngConstants
If cellz.Value >= MinVal And cellz.Value <= MaxVal Then
If n = 0 Then
Set myrange = cellz
n = 1
Else
Set myrange = Union(myrange, cellz)
End If
End If
Next cellz
myrange.Select
On Error GoTo endit
n = 0
For Each cellz In rngFormulas
If cellz.Value >= loval And cellz.Value <= hival Then
If n = 0 Then
Set myrange2 = cellz
n = 1
Else
Set myrange2 = Union(myrange2, cellz)
End If
End If
Next cellz
On Error Resume Next
Set myfinishedrange = Union(myrange, myrange2)
myfinishedrange.Select
Exit Sub
endit:
Set MyFinishedRange=MyRange
MyFinishedRange.Select
End Su
 
Top