Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

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

            Exit Sub
        End If
End Sub
share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .