Locate numbers 26 and higher

[NANCY SKYES]

Hello


This my first post so I hope this is clear.


I was looking for help to develop a macro that searches a large number of different ranges for values 26 and higher and to write the cell location results to cells B3, B4, B5 or B6. Some ranges may or may not have sets of numbers in them. I’m only interested in sets of numbers that have a value of 26 (2^nd number in a set of numbers) or higher in them. An example would be 8-26 or 7-35. there will always be data in some of the cell ranges. Please see my sample data below with expected result.


- I can not use a formula because their are issues with some other VBA code I run in the same cell range.
- I need hard code for the search ranges (I have many ranges in different locations on the sheet) so I can add or delete in the code as required. There is data above the cell ranges almost always.
- The sheet name is Sheet 1 and their is only one sheet in the workbook.
- Using Excel 2007.
- The search range cells are formatted as text.
- The write cells are formatted as general.


Sample data:


The code searches ranges D3:D100, F30:F120, H1:H122. The first found set of numbers would be
10-26 in cell D9 and the 2nd positive set of numbers would be 5-35 located in cell F40. The code would then write the cell location to cells B3, B4, B5 or B6. If their are NO positive results could the code write a 0 in cell B3 when finished.


If you have any questions please ask so I can clarify any issue(s).
Thanks so much for all your help.


[h=2][/h] <style type="text/css">h2 { direction: ltr; color: rgb(0, 0, 0); text-align: left; }h2.western { font-family: "Liberation Serif", sans-serif; }h2.cjk { font-family: "WenQuanYi Micro Hei"; }h2.ctl { font-family: "Lohit Devanagari"; }p { margin-bottom: 0.25cm; direction: ltr; color: rgb(0, 0, 0); line-height: 115%; text-align: left; }p.western { font-family: "Liberation Serif", serif; font-size: 12pt; }p.cjk { font-family: "WenQuanYi Micro Hei"; font-size: 12pt; }p.ctl { font-family: "Lohit Devanagari"; font-size: 12pt; }a:link { }</style>

 

[Rick Rothstein]

Hi Rick I just tested the code and had an issue with it so I thought I would try to debug the error 9, Subscript out of range on line “If Temp(1) > 26 Then”

I think that means there is a cell without a dash in it (perhaps just a number or a blank cell). I think this small (untested) modification to my code (shown in blue) will fix the problem...

Sub GreaterThan26()
  Dim X As Long, Rng As Range, Cell As Range, O As Variant, Temp As Variant
  Set Rng = Range("[B][COLOR="#FF0000"]D3:D100,F30:F120,H1:H122[/COLOR][/B]")
  ReDim O(1 To Rng.Count, 1 To 1)
  For Each Cell In Rng
    Temp = Split(Cell.Value[B][COLOR="#0000FF"] & "-"[/COLOR][/B], "-")
    If Temp(1) > 26 Then
      X = X + 1
      O(X, 1) = Cell.Address(0, 0)
    End If
  Next
  Range("B1").Resize(UBound(O), 1) = O
End Sub

 

[NANCY SKYES]

Hi Dante

I misunderstood your original post. Anyways, I just finished tested your code and it works perfectly. Thank-you so much

 

[NANCY SKYES]

Hi Rick

That change did the trick. Thank-you as well. You guys are just wonderful.
Nancy

 

[DanteAmor]

Hi Dante

I misunderstood your original post. Anyways, I just finished tested your code and it works perfectly. Thank-you so much

I'm glad to help you. Thanks for the feedback.