VBA code problem

[Mike Guest98]

Hi

I have the following VBA program that works but I would like to make two changes and just not having any luck. The first change is to have the existing "1-1" accept any number combination (say 1-99 or 99-1) right now it only accepts 1-1 as a positive result.

My second request is to the have the positive result be copied to cell G15. Thank-you in advance for your help


Sub do_it()

n = [C17]

For Each cell In Range("C30:C300,E30:E300,G30:G300,H30:H300,L30:L300")
If cell.Value = n And Range(cell.Address).Offset(0, 1) = "1-1" Then
MsgBox "Found a postivive result in " & cell.Address
End If
Next

End Sub <style type="text/css">pre { direction: ltr; color: rgb(0, 0, 10); text-align: left; }pre.western { font-family: "Liberation Mono", serif; }pre.cjk { font-family: "WenQuanYi Micro Hei Mono"; }pre.ctl { font-family: "Liberation Mono"; }p { margin-bottom: 0.1in; direction: ltr; color: rgb(0, 0, 10); line-height: 120%; 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; }</style>

 

[BiocideJ]

I believe to adequately do what you are asking, the solution needs to delve into regular expressions.

The following code will allow for basically any number on the format #-# (e.g. 0-0, 9999999-1038103830813078, etc)
if you need something more specific, please give the criteria of values should be accepted as a positive result. (i.e. should only numbers between 1 and 99 be accepted?)

Sub do_it()


n = [C17]
    Set reg = CreateObject("VBScript.RegExp")
    reg.Pattern = "^[0-9]*\-[0-9]*$"
    reg.Global = True
    For Each cell In Range("C30:C300,E30:E300,G30:G300,H30:H300,L30:L300")
        strVAL = cell.Offset(0, 1).Value
        If cell.Value = n And reg.test(strVAL) Then
            MsgBox "Found a postivive result in " & cell.Address
        End If


    Next


End Sub

 

[Mike Guest98]

I tried using your suggestion of #-# in my current with no luck. I also tried<code class="western"> "*#-#*" and that's no good also but the great news for me is your version of the program works. I should have mentioned that 1 to 99 would be the only numbers accepted. My last issue is to copy the adjacent column numbers to cell G15. In this case it had a positive result in cell C25 so I need to copy the </code><code class="western"><code class="western">adjacent </code>cell contents of D25 (5-35) to cell G15.</code> <style type="text/css">pre { direction: ltr; color: rgb(0, 0, 10); text-align: left; }pre.western { font-family: "Liberation Mono", serif; }pre.cjk { font-family: "WenQuanYi Micro Hei Mono"; }pre.ctl { font-family: "Liberation Mono"; }p { margin-bottom: 0.1in; direction: ltr; color: rgb(0, 0, 10); line-height: 120%; 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; }code.western { font-family: "Liberation Mono", serif; }code.cjk { font-family: "WenQuanYi Micro Hei Mono"; }code.ctl { font-family: "Liberation Mono"; }</style>

Thanks so much for your help.

 

[BiocideJ]

OK, I can make the changes, but I just wanted to clarify if multiple values match the criteria, which should be copied to G15? (e.g. first found, last found, etc.)

This will find the first result and stop

Sub do_it()




n = [C17]
    Set reg = CreateObject("VBScript.RegExp")
    reg.Pattern = "^([1-9][0-9]?)\-([1-9][0-9]?)$"
    reg.Global = True
    For Each cell In Range("C30:C300,E30:E300,G30:G300,H30:H300,L30:L300")
        strVAL = cell.Offset(0, 1).Value
        If cell.Value = n And reg.test(strVAL) Then
            Range("G15").Value = strVAL
            MsgBox "Found a postivive result in " & cell.Address
            Exit For    'remove this line to get last entry
        End If




    Next




End Sub

 

[Mike Guest98]

I just did a quick write up of the program goals (hope this is clear enough, if not please let me know:

I’m trying to find the location of an entered number, any number 1 to 100 is the choice. The number is entered in cell C17, say the number 4 is entered.

The program must search the following 5 column locations: C30:C300,E30:E300,G30:G300,H30:H300,L30:L300 for the number 4.



All 5 column ranges above have a number 4 located in them. What’s important is that there is only one number 4 with an adjacent cells to the right of it with numbers in them this would be considered a positive result. Just as above the numbers would be 1-100 or 99-4 etc.

Thanks again


<style type="text/css">p { margin-bottom: 0.1in; direction: ltr; color: rgb(0, 0, 10); 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; }</style>

 

[Mike Guest98]

Just finished trying it and it works great. Thanks again for all your help.

Mike