Written by Are you facing problems with long formulas? We are. So here are some articles, which are quite useful.
-Index and refering
MrExcel1
-ISerror and IsNA and a long formula
MrExcel2
-Exceed 7 Ifs
MrExcel3
ChipPearson1
-named ranges
ChipPearson1
Also, I created this function, which will find if any of the given values exists in the given range. Normally, we would use this
But, when the list and formula gets longer it becomes unreadable and takes time to calculate.
Instead you can use this function. Copy and paste the code in a module in the workbook you’re working on. Use this function in any cell where you want to check if multiple values are present or not.
For eg. If you want to check if cell A1 has any one of these values Chair, Interim Chair, Prof.
Enter this formula = Isvalpresent(A1,”Chair”,”Interim Chair”, “Prof”), it will return either True or False
It can take 9 values to check and can be easily increased by changing the code. This function will be very useful when you have long formulas.
Optional val4 As Variant = “”, Optional val5 As Variant = “”, Optional val6 As Variant = “”, Optional val7 As Variant = “”, Optional val8 As Variant = “”, Optional val9 As Variant = “”) As Boolean
‘
‘ IsvalPresent Macro
‘ Checks for a value if it is present in the given range. Use this function instead of IF and OR functions.
‘
‘
Dim c As Range, arr1 As Variant
arr1 = Array(val1, val2, val3, val4, val5, val6, val7, val8, val9)
For Each c In IPRange
For i = 0 To 8
If Not IsNothing(arr1(i)) Then
If c = arr1(i) Then
IsvalPresent = True
Exit Function
End If
End If
Next i
Next c
End Function
Public Function IsNothing(val As Variant) As Boolean
If val = “” Then IsNothing = True
End Function
Related posts:
Recent Comments