vb to search for a range of numbers

[zeus]

good afternoon,
I'm trying to write some macro that will search my spreadsheet for a range of accounts, if it finds even just one number in that range it then does a bunch of steps, if it can't find any accounts in that range then the macro should do nothing.

I've found a way that my goal can be accomplished by seaching for each account and if it finds it it's assigned a number then does a few other steps but I'm pretty sure this is a horrible way to write the macro, in addition, it would be very lengthy because I'd have to search for every single account. I've tried to seach the internet for some help but have come up empty.

As an example:
Search for any account in the range of 100000-300000
if there is a number in this range then do some additional steps involving checking if the corresponding 6xxxxx number is also in the spreadsheet, if it's not in the spreadsheet then I need to add it, if it already is in the spreadsheet then nothing is required.
If the spreadsheet doesn't contain any numbers between 100000-300000 then it does nothing.

One possible additional complexity is that the report shows the account names with some asterisks and the account name rather than just the account number:
* 100000 Cash
* 110000 Investments
** Total Cash & Other

Sorry for the long post. I know how to do the other steps I require, but I'm stuck on how to search for the range of numbers. I appreciate any assistance you might be able to provide

Thank you

 

[AnalyticsGuy7]

The following code may not be ideal, but I think it will do your trick.

Say your numbers are all in Column A, starting in A1...


Procedure:

1. Look at the first number.
2. Remove everything from it that isn't a number (uses a separate module that is called).
3. If that resulting number is within the range specified, then check to see if it's corresponding code with a "6" in front of it is available.
4. If not, Add it to the end of the list and move on to the next number.

Option Explicit
Public Looper As Integer
Public Outer As Integer
Public Newstring As String
Public MinRange As Variant
Public MaxRange As Variant

Sub Test()

MinRange = 100000
MaxRange = 300000

For Outer = 1 To Range("A" & Rows.Count).End(xlUp).Row

    Call RemoveText(Range("A" & Outer).Value)
    
    'Range("A" & Outer).Offset(0, 1) = Newstring
    
    If Newstring <> "" Then
        If Val(Newstring) >= MinRange And Val(Newstring) <= MaxRange Then
            MsgBox "Account code " & Val(Newstring) & " is within Range"
            Call FindCode(Val(Newstring))
        End If
    End If
    
    Newstring = ""
    
Next Outer

End Sub

Sub RemoveText(i As Variant)

    For Looper = 1 To Len(i)
    
        If Mid(i, Looper, 1) Like "#" Then
        Newstring = Newstring & Mid(i, Looper, 1)
        Else
            Newstring = Newstring & ""
        End If
        
    Next Looper
    
End Sub


Sub FindCode(a As Variant)
Dim c As Variant

With Range(Range("A1"), Range("A" & Rows.Count))
    Set c = .Find("6" & a, LookIn:=xlValues)
    If Not c Is Nothing Then
        MsgBox "Match Found"
     Else
        MsgBox "Match Not Found.  Adding to end of list..."
        Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = "6" & a
    End If
End With


End Sub

You can put ' in front of the msgboxes if you don't want to see the messages pop up.

Hope that helps!!

 

[AnalyticsGuy7]

Also, if you are saying that the corresponding code is 6xxxxx (replaces first number), then change the line of code in the first sub to:

Call FindCode(Val(Right(Newstring, Len(Newstring) - 1)))

 

[zeus]

Thank you so much AnalyticsGuy7! Sorry for the long delay in response, I had to take my work with me for the extended long weekend and I didn't have internet access to respond. The code you provided works great! I modified it a bit to work with the way our reports are set up (i.e. doing the search in column B rather than column A etc.) Of course I discovered oddities with the way we have set things up. There are instances where I need to look for any number within a range of numbers (as I previously posted) but in addition, I also need to look for a few numbers that actually fall within another range...all as part of the same search (i.e. 100000-300000, 435000, 502000, 535000). Would you be able to tell me if I can simply and easily modify the code you provided earlier or does this result in a brand new more involved and complex code? If I would need a more complex, involved code I think I might just be able to do it in a make-shift kinda way but it would work for now.

Thanks again!

 

[AnalyticsGuy7]

It is this line of code that you would want to modify:

If Val(Newstring) >= MinRange And Val(Newstring) <= MaxRange Then

Before the "Then", just add some extra conditions (parentheses are probably best).

for example.

If (Val(Newstring) >= MinRange And Val(Newstring) <= MaxRange) OR Val(Newstring) = 435000 OR Val(Newstring) = 502000 OR Val(Newstring) = 535000 Then

**I have not tested the code, but I think you get the idea. Just be careful about your parentheses.

 

[zeus]

yep, that does the trick, thank you!