creating range

[toon]

hi!

i want to create a (dynamic) range in a row of sorted values

eg 99 97 97 97 96 95 94 91 90 89 ...

MyRange1 = all the cells >95
MyRange2= all the cells less than 95, greater than 90
Myrange3= ....

how do i manage?

 

[Guest]

Try something like this.

Sub CRange()

Dim bigArray() As Integer
Dim midArray() As Integer
Dim bigCount, midCount As Integer

Range("A3").Select
Range(Selection, Selection.End(xlToRight)).Select

bigCount = 0
midCount = 0

For Each cell In Selection
If cell.Value > 95 Then
ReDim Preserve bigArray(bigCount)
bigArray(bigCount) = cell.Value
bigCount = bigCount + 1
End If
If cell.Value <= 95 And cell.Value >= 90 Then
ReDim Preserve midArray(midCount)
midArray(midCount) = cell.Value
midCount = midCount + 1
End If
Next cell

End Sub

Regards
Rowan

 

[Guest]

Hello,

There are a few possibilities, including use of the Find Method, here's one
using Match()

Sub tester()
Dim myRng1 As Range, myRng2 As Range
Dim myArr As Variant
Let myArr = Sheets(1).Range("a1:iv1").Value
With WorksheetFunction
Set myRng1 = Sheets(1).Range("a1").Resize(, .Match(96, myArr, -1))
Set myRng2 = Range(Sheets(1).Cells(1, .Match(94, myArr, -1)), _
Sheets(1).Cells(1, .Match(91, myArr, -1)))
End With
Debug.Print myRng1.Parent.Name, myRng1.Address, _
myRng2.Parent.Name, myRng2.Address
Set myRng1 = Nothing: Set myRng2 = Nothing
End Sub

Be careful with cells < 95, if Match with a -1 can't find 94, it will jump
up to 95.

From the help file re: Match:

"If match_type is -1, MATCH finds the smallest value that is greater than or
equal to lookup_value. Lookup_array must be placed in descending order: TRUE,
FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on. "

So you might want to test the return. You could specify an exact match with
0, and if it doesn't find your number, your procedure will error-out.

Regards,
Nate Oliver

 

[Guest]

Hello again,

The test is pretty straightforward, here's an example:

Sub tester2()
Dim myRng2 As Range
Dim myMtch1 As Long, myMtch2 As Long
Dim myArr As Variant
Let myArr = Sheets(1).Range("a1:iv1").Value
With WorksheetFunction
Let myMtch1 = .Match(94, myArr, -1)
Let myMtch2 = .Match(91, myArr, -1)
If Not myArr(1, myMtch1) = 94 Then _
myMtch1 = myMtch1 + 1
With Sheets(1)
Set myRng2 = Range(.Cells(1, myMtch1), _
.Cells(1, myMtch2))
End With
End With
Debug.Print myRng2.Parent.Name, myRng2.Address
Set myRng2 = Nothing
End Sub

You only have to test the upper boundary, the Match algorithm has the lower
boundary test baked into it for you when you specify a match type of -1.

Regards,
Nate Oliver