Hi Team,
request you to if there is any function in your mind or macro to solve the below rather than getting hang excel every time ...
I have 5000+ rows to validate the below formula due to which its taking too much time and sometime excel get hanged.. [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 395"]
[tr][td]S.No
[/td][td]Code ID
[/td][td]Time
[/td][td]Case
[/td][td]Result
[/td][/tr][tr][td]1
[/td][td]1234567890P
[/td][td]10:30
[/td][td]abcde
[/td][td]Morning In
[/td][/tr][tr][td]2
[/td][td]1234567890P
[/td][td]5:30
[/td][td]abspl
[/td][td]On Demand In
[/td][/tr]
[/TABLE]
Result come from below formula...
=IF(AND(SplitText(B2,FALSE)="D",COUNTIF(D2,"*spl*")>0),"On Demand out",IF(AND(SplitText(B2,FALSE)="P",COUNTIF(D2,"*spl*")>0),"On Demand In",IF(AND(SplitText(B2,FALSE)="D",OR(AND(C2>=(0/24),C2<=(7/24)),AND(C2>=(20/24),C2<=(40/24)))),"Night out",IF(AND(SplitText(B2,FALSE)="P",OR(AND(C2>=(21/24),C2<=(25/24)),AND(C2>=(0/24),C2<=(7/24)))),"Night In",IF(AND(SplitText(B2,FALSE)="D",OR(AND(C2>(14/24),C2<=(19.5/24)))),"Morning Out",IF(AND(SplitText(B2,FALSE)="P",OR(AND(C2>=(7.5/24),C2<=(19.5/24)))),"Morning In",IF(SplitText(B2,FALSE)="DA","Case Out",IF(SplitText(B2,FALSE)="PA","Case In","Error"))))))))
above if condition after paste till 5000+ rows each time ...
plz help .. Thanks ...
and for split text I have a function which got me on our forum..
#######
Public Function SplitText(pWorkRng As Range, pIsNumber As Boolean) As String
Dim xLen As Long
Dim xStr As String
xLen = VBA.Len(pWorkRng.Value)
For i = 1 To xLen
xStr = VBA.Mid(pWorkRng.Value, i, 1)
If ((VBA.IsNumeric(xStr) And pIsNumber) Or (Not (VBA.IsNumeric(xStr)) And Not (pIsNumber))) Then
SplitText = SplitText + xStr
End If
Next
End Function