sathish_fun
07-10-2008, 10:06 AM
HI i am using the codes which will check two colums in a sheet if "u" = others and colmn "V"= SUDHIR then in colum "BH" it shoul print as "Venkat Team " codes as mentioned below but it is taking to much time when i am checking more than 14 thousand rows.It is taking more than 5 min to give out put,so can u help 2 work faster or any other way
My Codes
Dim r As Range, i As Long
Set r = Intersect(ActiveSheet.UsedRange, Range("U:V"))
If Not r Is Nothing Then
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "SUDHIR" Then _
Cells(i, "BH").Value = "Venkat Team "
Next
Application.EnableEvents = True
End If
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-PBKARTHICK" Then _
Cells(i, "BH").Value = "Krishnakumar Team "
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-VIMAL" Then _
Cells(i, "BH").Value = "Krishnakumar Team "
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-SAKTHIVEL" Then _
Cells(i, "BH").Value = "Krishnakumar Team "
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-SRINIVASAN NAGARAJ" Then _
Cells(i, "BH").Value = "Krishnakumar Team "
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-RASOOL" Then _
Cells(i, "BH").Value = "Krishnakumar Team "
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-JAYAPRAKASH" Then _
Cells(i, "BH").Value = "Krishnakumar Team "
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-SATHYANANDHAN" Then _
Cells(i, "BH").Value = "Krishnakumar Team "
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-VELU.M" Then _
Cells(i, "BH").Value = "Krishnakumar Team "
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-KANNAPIRANPAS" Then _
Cells(i, "BH").Value = "Krishnakumar Team "
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-PANDARINAATHAN.V" Then _
Cells(i, "BH").Value = "Krishnakumar Team "
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-SARAVANAN.S" Then _
Cells(i, "BH").Value = "Krishnakumar Team "
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-SANTHOSH" Then _
Cells(i, "BH").Value = "Krishnakumar Team "
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-P.SRINIVASAN" Then _
Cells(i, "BH").Value = "Krishnakumar Team "
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-RAMKUMAR" Then _
Cells(i, "BH").Value = "Krishnakumar Team "
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-SPALANISAMY" Then _
Cells(i, "BH").Value = "Krishnakumar Team "
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-GEORGE PREMARANJAN" Then _
Cells(i, "BH").Value = "Krishnakumar Team "
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-J.BALAMANIKANDAN TEAM" Then _
Cells(i, "BH").Value = "Krishnakumar Team "
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-P.SURESH" Then _
Cells(i, "BH").Value = "Krishnakumar Team "
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-G.RAJESH" Then _
Cells(i, "BH").Value = "Krishnakumar Team "
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-R SATHISH KUMAR" Then _
Cells(i, "BH").Value = "Krishnakumar Team "
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-T.S.SURESHKUMAR" Then _
Cells(i, "BH").Value = "Krishnakumar Team "
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-LOGANATHAN" Then _
Cells(i, "BH").Value = "Krishnakumar Team "
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-ASHFAG" Then _
Cells(i, "BH").Value = "MANIKANDAN TEAM"
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-N.DINESH" Then _
Cells(i, "BH").Value = "MANIKANDAN TEAM"
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-ELANGAIVENDAN" Then _
Cells(i, "BH").Value = "MANIKANDAN TEAM"
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-SP SIVAKUMAR" Then _
Cells(i, "BH").Value = "MANIKANDAN TEAM"
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-V.V.VIJAY KUMAR" Then _
Cells(i, "BH").Value = "MANIKANDAN TEAM"
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-KENNEDY" Then _
Cells(i, "BH").Value = "MANIKANDAN TEAM"
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-GR KHAN" Then _
Cells(i, "BH").Value = "MANIKANDAN TEAM"
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-DIVAKAR.R" Then _
Cells(i, "BH").Value = "MANIKANDAN TEAM"
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-SANJAY" Then _
Cells(i, "BH").Value = "MANIKANDAN TEAM"
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE- M.YUVARAJ" Then _
Cells(i, "BH").Value = "MANIKANDAN TEAM"
Next
End Sub
Edited by Aussiebear: sathish_fun, please use the VBA tag button to enclose your code. It makes it so much easier to read.
My Codes
Dim r As Range, i As Long
Set r = Intersect(ActiveSheet.UsedRange, Range("U:V"))
If Not r Is Nothing Then
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "SUDHIR" Then _
Cells(i, "BH").Value = "Venkat Team "
Next
Application.EnableEvents = True
End If
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-PBKARTHICK" Then _
Cells(i, "BH").Value = "Krishnakumar Team "
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-VIMAL" Then _
Cells(i, "BH").Value = "Krishnakumar Team "
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-SAKTHIVEL" Then _
Cells(i, "BH").Value = "Krishnakumar Team "
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-SRINIVASAN NAGARAJ" Then _
Cells(i, "BH").Value = "Krishnakumar Team "
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-RASOOL" Then _
Cells(i, "BH").Value = "Krishnakumar Team "
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-JAYAPRAKASH" Then _
Cells(i, "BH").Value = "Krishnakumar Team "
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-SATHYANANDHAN" Then _
Cells(i, "BH").Value = "Krishnakumar Team "
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-VELU.M" Then _
Cells(i, "BH").Value = "Krishnakumar Team "
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-KANNAPIRANPAS" Then _
Cells(i, "BH").Value = "Krishnakumar Team "
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-PANDARINAATHAN.V" Then _
Cells(i, "BH").Value = "Krishnakumar Team "
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-SARAVANAN.S" Then _
Cells(i, "BH").Value = "Krishnakumar Team "
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-SANTHOSH" Then _
Cells(i, "BH").Value = "Krishnakumar Team "
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-P.SRINIVASAN" Then _
Cells(i, "BH").Value = "Krishnakumar Team "
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-RAMKUMAR" Then _
Cells(i, "BH").Value = "Krishnakumar Team "
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-SPALANISAMY" Then _
Cells(i, "BH").Value = "Krishnakumar Team "
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-GEORGE PREMARANJAN" Then _
Cells(i, "BH").Value = "Krishnakumar Team "
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-J.BALAMANIKANDAN TEAM" Then _
Cells(i, "BH").Value = "Krishnakumar Team "
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-P.SURESH" Then _
Cells(i, "BH").Value = "Krishnakumar Team "
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-G.RAJESH" Then _
Cells(i, "BH").Value = "Krishnakumar Team "
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-R SATHISH KUMAR" Then _
Cells(i, "BH").Value = "Krishnakumar Team "
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-T.S.SURESHKUMAR" Then _
Cells(i, "BH").Value = "Krishnakumar Team "
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-LOGANATHAN" Then _
Cells(i, "BH").Value = "Krishnakumar Team "
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-ASHFAG" Then _
Cells(i, "BH").Value = "MANIKANDAN TEAM"
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-N.DINESH" Then _
Cells(i, "BH").Value = "MANIKANDAN TEAM"
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-ELANGAIVENDAN" Then _
Cells(i, "BH").Value = "MANIKANDAN TEAM"
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-SP SIVAKUMAR" Then _
Cells(i, "BH").Value = "MANIKANDAN TEAM"
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-V.V.VIJAY KUMAR" Then _
Cells(i, "BH").Value = "MANIKANDAN TEAM"
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-KENNEDY" Then _
Cells(i, "BH").Value = "MANIKANDAN TEAM"
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-GR KHAN" Then _
Cells(i, "BH").Value = "MANIKANDAN TEAM"
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-DIVAKAR.R" Then _
Cells(i, "BH").Value = "MANIKANDAN TEAM"
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE-SANJAY" Then _
Cells(i, "BH").Value = "MANIKANDAN TEAM"
Next
Application.EnableEvents = True
Application.EnableEvents = False
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" And UCase(Cells(i, "V").Value) = "CORPORATE- M.YUVARAJ" Then _
Cells(i, "BH").Value = "MANIKANDAN TEAM"
Next
End Sub
Edited by Aussiebear: sathish_fun, please use the VBA tag button to enclose your code. It makes it so much easier to read.