I used conditional IF to write my formulas but when my table increase in size, it became many IF statements in a cell.
I tried to replace IF with lookup function but some how I got so many ERR.
Can someone write me the formulas to solve my issue?Here is the example:
It can be a row or column but let assume it is a row A1, B1, C1, etc.
Ie. A1 = -25, B1 = 0, and C1 = 31.We have another row consist of A7 to AA7 for example.
Now we want to search through A7 to AA7 to find a value between A1 and B1(between -25 to 0), B1 and C1 (between 0 to 31) etc…
If it does found, it will display the found value in a cell in question.
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | 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 |
| Form regulation with the shortcut keys |
| Fix the size of table in Word |
| Frequently Asked Questions Excel solution |
| How to use Excel VLookup Function |
