How to find the first value in a range that is greater than x

[harshdua]

I currently have a macro set up to find values in a range, however some values dont exist in the column and as such i end up getting an error 91. To get around this, i want it to find the next range. My current macro is as follows.

Sub xfg()
Dim findnextGL As Range
Dim nextGL As Integer
nextGL = 51
Do Until nextGL = 1001
Set findnextGL = Range("B02:B4899").Find(nextGL, LookIn:=xlValues)
If findnextGL Is Nothing Then

Debug.Print error 91

Exit Sub
Else
Debug.Print findnextGL.Address
nextGL = nextGL + 50

End If
Loop
End Sub

 

[davesexcel]

What happens if you don't exit the sub?
Just do nothing if the findnextGL is nothing

 

[harshdua]

What happens if you don't exit the sub?
Just do nothing if the findnextGL is nothing

Then it does the loop again, and skips over the nearest value that is equal to the current nextGL

 

[Fluff]

You need to move this line outside of the loop

nextGL = nextGL + 50

and get rid of the Exit Sub as Dave suggested.

 

[harshdua]

You need to move this line outside of the loop

nextGL = nextGL + 50

and get rid of the Exit Sub as Dave suggested.

Sorry i worded my question poorly

I want the macro to find the first value that is greater than nextGL within the range of findnextGL if findnextGL is nothing

 

[Fluff]

In that case replace Exit Sub with

nextGL = nextGL + 1

assuming your values are whole numbers

 

[harshdua]

In that case replace Exit Sub with

nextGL = nextGL + 1

assuming your values are whole numbers

This however changes the current value of nextGL and disrupts the search i have set up

I need to find the addresses of the cells that are equal to every number that satisfies the following expression, (51+50n)
ie: i need it to find the cell equal to (51,101,151,201...)
in case it doesnt exist, i need it to find the first value that is greater than nextGL within the range. For example, if my column is of the following, within the first loop, since 51 does not exist, findnextGL is set to equal to the cell of 54, and it continues and finds me the cells of 101,151,201...

50

54

67

59

101

151

201

 

[Fluff]

How about

Sub xfg()
   Dim findnextGL As Range
   Dim nextGL As Long, GLinc As Long
   nextGL = 51
   Do Until nextGL = 1001
      Set findnextGL = Range("B2:B4899").Find(nextGL, LookIn:=xlValues)
      If findnextGL Is Nothing Then
         GLinc = nextGL
         Do Until GLinc = nextGL + 49
            GLinc = GLinc + 1
            Set findnextGL = Range("B2:B4899").Find(GLinc, LookIn:=xlValues)
            If Not findnextGL Is Nothing Then
               Debug.Print findnextGL.Address, GLinc
               Exit Do
            End If
         Loop
      Else
         Debug.Print findnextGL.Address
      End If
      nextGL = nextGL + 50
   Loop
End Sub

 

[harshdua]

How about

Sub xfg()
   Dim findnextGL As Range
   Dim nextGL As Long, GLinc As Long
   nextGL = 51
   Do Until nextGL = 1001
      Set findnextGL = Range("B2:B4899").Find(nextGL, LookIn:=xlValues)
      If findnextGL Is Nothing Then
         GLinc = nextGL
         Do Until GLinc = nextGL + 49
            GLinc = GLinc + 1
            Set findnextGL = Range("B2:B4899").Find(GLinc, LookIn:=xlValues)
            If Not findnextGL Is Nothing Then
               Debug.Print findnextGL.Address, GLinc
               Exit Do
            End If
         Loop
      Else
         Debug.Print findnextGL.Address
      End If
      nextGL = nextGL + 50
   Loop
End Sub

I Love you man <3 no ****

 

[Fluff]

You're welcome & thanks for the feedback