PDA

View Full Version : Need Help



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.

Norie
07-10-2008, 10:29 AM
Why are you looping so many times?

sathish_fun
07-10-2008, 10:36 AM
can u help i badly need it:help

mae0429
07-10-2008, 02:37 PM
Have you thought of a layout something more like this? (Untested)


Dim r As Range, i As Long, testStr As String
Set r = Intersect(ActiveSheet.UsedRange, Range("U:V"))
If Not r Is Nothing Then
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" Then
testStr = UCase(Cells(i, "V").Value)
Select Case testStr
Case Is = "SUDHIR"
Cells(i, "BH").Value = "Venkat Team"
Case Is = "CORPORATE-PBKARTHICK", "CORPORATE-VIMAL" ', and so on and so forth for all the cases that give "Krishnakumar Team"
Cells(i, "BH").Value = "Krishnakumar Team "
'.....Continue for all your cases
Case Else
End Select
End If
Next i
End If

sathish_fun
07-11-2008, 02:46 AM
Thanks working fine:hi:

sathish_fun
07-11-2008, 04:36 AM
as u mentioned i am using the codes as mentioned below but it is not giving out put


Dim r As Range, i As Long, testStr As String
Set r = Intersect(ActiveSheet.UsedRange, Range("U"))
If Not r Is Nothing Then
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" Then
testStr = UCase(Cells(i, "V:V").Value)
Select Case testStr
Case Is = _
"CORPORATE-ASHFAG", "CORPORATE-N.DINESH", "CORPORATE-ELANGAIVENDAN", "CORPORATE-SP SIVAKUMAR", "CORPORATE-V.V.VIJAY KUMAR", "CORPORATE-KENNEDY", "CORPORATE-GR KHAN", "CORPORATE-DIVAKAR.R", "CORPORATE-ABDUL RAHIM", "CORPORATE- M.YUVARAJ", "CORPORATE-MANI", "TEAM LEADER - MANIKANDAN", "CORPORATE-NARAYANA MOORTHI", "CORPORATE-P.BALAJI", "CORPORATE-VENKATESH BABU", "CORPORATE-ARUN KUMAR", "EXTRA", "EXTRA", "EXTRA", "EXTRA", "CORPORATE - S.SOUNDARAPANDIYAN", "CORPORATE-D.VIJAYAKUMAR", "TEAM LEADER - SOUNDAR", "CORPORATE-G. SENTHIL ANAND", "CORPORATE-V.SAIRAM", "CORPORATE-KSP", "CORPORATE-VISWANATHAN DILIP", "CORPORATE-D.S.KUMAR", "CORPORATE-SRINIVASAN.N", "EXTRA", "CORPORATE-SIVA", "EXTRA", "CORPORATE-SATHEESH SRINIVASAN", "EXTRA", "TEAM LEADER - SIVARAMAKRISHNAN", "CORPORATE-DHANASEKAR", "CORPORATE-MAHESH BABU", "CORPORATE-PRAVEEN GIRIYA", "CORPORATE-MEENAKSHI SUNDARAM", "CORPORATE-SATHISHKUMAR", "CORPORATE-VIJAY AMIRTHARAJ", _
"CORPORATE-V.NAGARAJ", "CORPORATE-VIJAY GANESH", "CORPORATE-SUBHASH", "TEAM LEADER - PRAVEEN GIRIYA", "VENKAT TEAM ", "CORPORATE TOTAL"
Cells(i, "BH").Value = "Venkat Team"
Case Is = "CORPORATE-PBKARTHICK", "CORPORATE-VIMAL", "CORPORATE-MAKESH RAJA M.P", "CORPORATE-MICHAEL", "CORPORATE-JAYAPRAKASH", "CORPORATE-SATHYANANDHAN", "CORPORATE-VELU.M", "CORPORATE-KANNAPIRANPAS", "CORPORATE-PANDARINAATHAN.V", "CORPORATE-DINESH SHARMA", "CORPORATE-SURESH ", "CORPORATE-SANTHOSH", "CORPORATE-KARTHIK.S", "CORPORATE-RAMKUMAR", "CORPORATE-SPALANISAMY", "CORPORATE-GEORGE PREMARANJAN", "CORPORATE-J.BALAMANIKANDAN", "CORPORATE-P.SURESH", "CORPORATE-P.SRINIVASAN", "CORPORATE-R SATHISH KUMAR", "CORPORATE-T.S.SURESHKUMAR", "CORPORATE-NARAYANAN", "CORPORATE-LOGANATHAN", "TEAM LEADER - LOGANATHAN", "CORPORATE-SAKTHIVEL", "CORPORATE-SARAVANAN.S", "CORPORATE-MADAN PRASAD", "CORPORATE-VASU"
'"CORPORATE-PBKARTHICK", "CORPORATE-VIMAL" ', and so on and so forth for all the cases that give "Krishnakumar Team"
Cells(i, "BH").Value = "Krishnakumar Team "
Case Is = "SME", "SME-MANIKANDAN", "SME-T.SAJITH", "SME-N. NIXON DARWIN ALBERT", "SME-BALAMURUGAN", "SME-VEERAUDIRAMALAI ", "SME - VENKATESH", "SME-SHAHJAHAN", "EXTRA", "SME-ASHOK", "SME-VENKATESH", "TEAM LEADER - VENKATESH", "SME-P VINOTH", "SME-A.PRABHU", "SME-R.NARESH", "SME-MANIKANDAN.G", "SME-VIVEKANANDAN", "SME-K.P.RAJA", "SME-RAMESH M", "EXTRA", "SME-M.SHANKAR PANDI", "EXTRA", "SME-PRAKASH.R", "TEAM LEADER - R PRAKASH", "SME-S.HARISH", "SME-P.MARIMUTHU", "SME-M SATHISH", "SME-T.DIVEK", "SME-NATARAJAN", "SME-MUTHUKUMAR", "SME-B.SURESH", "SME-JSWAMINATHAN", "EXTRA", "SME-NARAYANAN", "TEAM LEADER - V NARAYANAN"
Cells(i, "BH").Value = "Partha"
'.....Continue for all your cases
Case Else
End Select
End If
Next i
End If
End Sub

Bob Phillips
07-11-2008, 04:49 AM
First you say


Thanks working fine:hi:

and then you say


as u mentioned i am using the codes as mentioned below but it is not giving out put

SO which is it, and if the latter, you need to explain yourself much better.

sathish_fun
07-11-2008, 04:54 AM
It Is Working when all the cell in range "U:U" Is "Others" but if the Range "U:U" Has Some other data then it is not showing the out put.My excel has the data of both name of the person and other in the same range "U:U"

sathish_fun
07-11-2008, 05:55 AM
kindly help :help

mae0429
07-11-2008, 06:49 AM
It Is Working when all the cell in range "U:U" Is "Others" but if the Range "U:U" Has Some other data then it is not showing the out put.My excel has the data of both name of the person and other in the same range "U:U"

But I thought you said:


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...

Just to clarify, what is this supposed to do if you have something other than "Others" in column U?

Bob Phillips
07-11-2008, 07:56 AM
You have never specified what is to happen if U holds anything other than Others, so how are we suppoosed to help?

Aussiebear
07-11-2008, 05:42 PM
sathish_fun, this is what I meant by using the vba tags button....

sathish_fun
07-14-2008, 05:25 AM
thanks found a way working fine

Dim r As Range, i As Long, testStr As String
Set r = Intersect(ActiveSheet.UsedRange, Range("U:V"))
If Not r Is Nothing Then
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" Then
testStr = UCase(Cells(i, "V").Value)
Select Case testStr
Case Is = "CORPORATE - S.SOUNDARAPANDIYAN", "CORPORATE-ARUN KUMAR", "CORPORATE-D.S.KUMAR", "CORPORATE-D.VIJAYAKUMAR", "CORPORATE-DHANASEKAR", "CORPORATE-G. SENTHIL ANAND", "CORPORATE-KSP", "CORPORATE-MAHESH BABU", "CORPORATE-MEENAKSHI SUNDARAM", "CORPORATE-NARAYANA MOORTHI", "CORPORATE-P.BALAJI", "CORPORATE-PRAVEEN GIRIYA", "CORPORATE-SATHEESH SRINIVASAN", "CORPORATE-SATHISHKUMAR", "CORPORATE-SIVA", "CORPORATE-SRINIVASAN.N", "CORPORATE-SUBHASH", "CORPORATE-V.NAGARAJ", "CORPORATE-V.SAIRAM", "CORPORATE-VENKATESH BABU", "CORPORATE-VIJAY GANESH", "CORPORATE-VIJAYAMIRTHARAJ ", "CORPORATE-VISWANATHAN DILIP"
Cells(i, "BH").Value = "VENKAT TEAM"
Case Else
End Select
End If
Next i
End If
If Not r Is Nothing Then
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" Then
testStr = UCase(Cells(i, "V").Value)
Select Case testStr
Case Is = "CORPORATE-ASHFAG", "CORPORATE-N.DINESH", "CORPORATE-ELANGAIVENDAN", "CORPORATE-SP SIVAKUMAR", "CORPORATE-V.V.VIJAY KUMAR", "CORPORATE-KENNEDY", "CORPORATE-GR KHAN", "CORPORATE-DIVAKAR.R", "CORPORATE-ABDUL RAHIM", "CORPORATE- M.YUVARAJ", "CORPORATE-MANI"
Cells(i, "BH").Value = "MANIKANDAN TEAM"
Case Else
End Select
End If
Next i
End If
If Not r Is Nothing Then
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" Then
testStr = UCase(Cells(i, "V").Value)
Select Case testStr
Case Is = "CORPORATE-DINESH SHARMA", "CORPORATE-GEORGE PREMARANJAN", "CORPORATE-J.BALAMANIKANDAN", "CORPORATE-JAYAPRAKASH", "CORPORATE-KANNAPIRANPAS", "CORPORATE-KARTHIK.S", "CORPORATE-LOGANATHAN", "CORPORATE-MADAN PRASAD", "CORPORATE-MAKESH RAJA M.P", "CORPORATE-MICHAEL", "CORPORATE-NARAYANAN", "CORPORATE-P.SRINIVASAN", "CORPORATE-P.SURESH", "CORPORATE-PANDARINAATHAN.V", "CORPORATE-PBKARTHICK", "CORPORATE-R SATHISH KUMAR", "CORPORATE-RAMKUMAR", "CORPORATE-SAKTHIVEL", "CORPORATE-SANTHOSH", "CORPORATE-SARAVANAN.S", "CORPORATE-SATHYANANDHAN", "CORPORATE-SPALANISAMY", "CORPORATE-SURESH ", "CORPORATE-T.S.SURESHKUMAR", "CORPORATE-VASU", "CORPORATE-VELU.M", "CORPORATE-VIMAL", "CORPORATE-G.RAJESH"
Cells(i, "BH").Value = "KRISHNAKUMAR TEAM"
Case Else
End Select
End If
Next i
End If
If Not r Is Nothing Then
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" Then
testStr = UCase(Cells(i, "V").Value)
Select Case testStr
Case Is = "SME-G.RAJASEKAR", "SME - VENKATESH", "SME T.MUTHUKUMAR", "SME-A.PRABHU", "SME-ASHOK", "SME-B.SURESH", "SME-BALAMURUGAN", "SME-JSWAMINATHAN", "SME-K.P.RAJA", "SME-M SATHISH", "SME-M.RAMESH", "SME-MANIKANDAN", "SME-MANIKANDAN.G", "SME-N. NIXON DARWIN ALBERT", "SME-NARAYANAN", "SME-NATARAJAN", "SME-P VINOTH", "SME-P.MARIMUTHU", "SME-PRAKASH.R", "SME-R.NARESH", "SME-S.HARISH", "SME-SANKARAPANDIAN", "SME-SHAHJAHAN", "SME-T.DIVEK", "SME-T.SAJITH", "SME-VEERAUDIRAMALAI ", "SME-VENKATESH", "SME-VIVEKANANDAN", "SME-G.Rajasekar"
Cells(i, "BH").Value = "PARTHA"
Case Else
End Select
End If
Next i
End If
If Not r Is Nothing Then
For i = r(1).Row To r(r.Count).Row
If UCase(Cells(i, "U").Value) = "OTHERS" Then
testStr = UCase(Cells(i, "V").Value)
Select Case testStr
Case Is = "SME-ThalaivarChokkalingam", "SME-Thiyagarajan", "SME-T.S.Mahendran", "SME-K.Sathish Kumar", "SME-N.Siva", "SME-Baskaran ", "SME-Magesh"
Cells(i, "BH").Value = "SRIRAM"
Case Else
End Select
End If
Next i
End If
End Sub

Regards
Sathishv