Substitute conditional IF function
Closed
David
-
Sep 12, 2009 at 10:18 AM
David Scott Posts 1 Registration date Friday September 11, 2009 Status Member Last seen September 13, 2009 - Sep 13, 2009 at 11:00 AM
David Scott Posts 1 Registration date Friday September 11, 2009 Status Member Last seen September 13, 2009 - Sep 13, 2009 at 11:00 AM
Related:
- Substitute conditional IF function
- Agp function - Guide
- Network card function - Guide
- Find function on mac - Guide
- Function of insert key - Guide
- Conditional formatting if two cells match ✓ - Excel Forum
2 responses
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Sep 12, 2009 at 08:58 PM
Sep 12, 2009 at 08:58 PM
You did not give complete informational. If a cell in range A1:AA77 is between A1 and B1 what should I do
quote
If it does found, it will display the found value in a cell in question.
unquote
what is meant by "display" and "cell in question"
tell me this
1.suppose F7 is found to be between a1 and B1 what should I do???????
2. suppose more than one cell in A7:AA7 which are between a1 and b1 what should I do with all these cells
the macro I have given below will find the cell in A7:AA7 and give its address.
It will continue to loop with all the cells in A1:AA7 and
then loop next
again start finding out whether any cell in A7:AA7 is betwen B1 and C1 and so on till all the cells are exhausted
You modify the macro to suit ou.
quote
If it does found, it will display the found value in a cell in question.
unquote
what is meant by "display" and "cell in question"
tell me this
1.suppose F7 is found to be between a1 and B1 what should I do???????
2. suppose more than one cell in A7:AA7 which are between a1 and b1 what should I do with all these cells
the macro I have given below will find the cell in A7:AA7 and give its address.
It will continue to loop with all the cells in A1:AA7 and
then loop next
again start finding out whether any cell in A7:AA7 is betwen B1 and C1 and so on till all the cells are exhausted
You modify the macro to suit ou.
Sub test()
Dim rng As Range, c As Range, rng1 As Range, c1 As Range
Dim x1, x2
With Worksheets("sheet1")
Set rng = Range(.Range("a1"), .Range("a1").End(xlToRight)).Offset(0, -1)
For Each c In rng
x1 = c.Value
x2 = c.Offset(0, 1).Value
x1 = WorksheetFunction.Min(x1, x2)
x2 = WorksheetFunction.Max(x1, x2)
Set rng1 = .Range("a7:aa7")
For Each c1 In rng1
If c1 >= x1 And c1 <= x2 Then
MsgBox c1.Address
End If
Next c1
Next c
End With
End Sub
David Scott
Posts
1
Registration date
Friday September 11, 2009
Status
Member
Last seen
September 13, 2009
Sep 13, 2009 at 11:00 AM
Sep 13, 2009 at 11:00 AM
Sorry for failing short of expanding the detail.
1 If it does found, it will display the found value in a cell in question.
In our sample we have:
A1 = -25, B1 = 0, and C1 = 31
Now, suppose from A7 to AA7 contains the following value:
-20, -7, 12, 15, 25, and 31.
Let said we test in cell B7, C7, D7, and E7 for the value between A1 and B1.
Thus, we would have -20 in cell B7, -7 in C7, and so on.
Let we test the result of B1 to C1 in cell B8, C8, D8, E8, and so on.
In this case, we would have a value 12 in B8, 15 in C8, 25 in D8, 31 in E8, and so forth.
Again sorry.
Thanks.
1 If it does found, it will display the found value in a cell in question.
In our sample we have:
A1 = -25, B1 = 0, and C1 = 31
Now, suppose from A7 to AA7 contains the following value:
-20, -7, 12, 15, 25, and 31.
Let said we test in cell B7, C7, D7, and E7 for the value between A1 and B1.
Thus, we would have -20 in cell B7, -7 in C7, and so on.
Let we test the result of B1 to C1 in cell B8, C8, D8, E8, and so on.
In this case, we would have a value 12 in B8, 15 in C8, 25 in D8, 31 in E8, and so forth.
Again sorry.
Thanks.
