Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I am trying to do the following:

one sheet |   in another sheet
-----------------------------------
 C        |    R     S
448       |   497   517
          |   526   542
453       |   812   814

can I check if C is between any value in all lines for R and S and then perform an operation if is it true?

an example for if =if(C(1)<=R(:)&&C(1)>=S(:))

share|improve this question
    
Please expand or clarify your question. You want to know if a value in c is between any two values in a row of RS or a column of R or S? –  Raystafarian Jul 15 '13 at 12:29
    
What if the value in C is between more than one set? –  Raystafarian Jul 18 '13 at 17:33
    
In what cell do you want the formula? –  Excellll Jul 18 '13 at 20:14

1 Answer 1

This is clunky and awkward, but it works, just change the ranges and the do stuff

Someone might come along and make it better so that it stops checking after the final condition is met.

Option Explicit

Sub sks()
    Dim Rng, Rng2, Rng3, c1, c2, c3 As Range

    Set Rng = Sheets("Sheet1").Range("A:A")
    Set Rng2 = Sheets("Sheet2").Range("A:A")
    Set Rng3 = Sheets("sheet2").Range("B:B")

    For Each c1 In Rng

        If c1.Value <> "" Then

            For Each c2 In Rng2

                If c2.Value <> "" Then

                   If c1.Value >= c2.Value Then

                      If c1.Value <= c2.Offset(0, 1).Value Then

                          'do stuff c1.Offset(0, 1).Value = "yes"

                       Else: End If
                    Else: End If
                Else: End If

             Next c2

          Else
            Exit Sub
        End If
    Next
End Sub
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.