Hi Anish
If you are simplifying the code, you can even reduce your code to less than
20 lines from 100s of IF conditions
1) Create a sheet named "Replace Values"
2) Make 3 columns with the headings:
a) "Find Text" (col A)
b) "Replace Text" (col B)
c) "Alternative text" (col C)
3) Write in (Col A) values you want to find and replace (Use the wild cards
here and not in the code! you may write "*SALES*" or *Sales?????*" or other
patterns)
4) Write in (Col B) the new value to be written if the condition is
satisfied
5) Write in (Col C) the new value to be written if the condition is NOT
satisfied
6) Now write the following code:
j=1
'write your While-wend loop here!
'------------------------------------------------
For i = 2 to sheets("Replace Values").cells(1,65000).end(xlup).row
if cells(j,1) like Sheets("Replace Values").cells(i,1) then
cells(j,1)=Sheets("Replace Values").cells(i,2)
else
cells(j,1)=Sheets("Replace Values").cells(i,3)
endif
Next i
'---------------------------------------------------
j = j+1
'Wend!
I have not tested this code yet.
Pls test and let me know if its not working
2009/10/26 Hemant Hegde <hemantbales...@gmail.com>
> Hi Anish
>
> Its not the problem with "WHILE" or "WEND"
>
> Make sure you have closed all "IF" conditions with "END IF" at proper
> places
>
> Just check if you have started an IF condition inside the loop and tried to
> close it after the loop
>
> 2009/10/26 Anish Shrivastava <anish....@gmail.com>
>
>> Hello Experts..
>>
>> I m using While and Wend statement in my code and it has got more than 100
>> if conditions. when I run the macro it says "WEND WITHOUT WHILE", it's
>> buggin my head...and I don know wht's wrong. Could anyone of you please help
>> me...
>>
>> Sample code...
>>
>> Sub dept()
>> i = 1
>> z = Cells(Rows.Count, "B").End(xlUp).Row
>> While i <= z
>> If Cells(i, 2).Value Like "*FINANCE*" Then
>> Cells(i, 4) = "FINANCE / ACCOUNTING"
>> Else
>> If Cells(i, 2).Value Like "*ACCOUNTS*" Then
>> Cells(i, 4) = "FINANACE / ACCOUNTING"
>> Else
>> If Cells(i, 2).Value Like "*SALES*" Then
>> Cells(i, 4) = "SALES"
>> *(IN BETWEEN THR R MORE THAN 100 IF CONDITIONS LIKE THIS)*
>> endif
>> endif
>> endif
>> i=i+1
>> wend
>> end sub
>>
>> -best
>> ANISH
>>
>>
>
>
> --
> Hemant Hegde
>
> >
>
--
Hemant Hegde
--~--~---------~--~----~------------~-------~--~----~
----------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,500 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
-~----------~----~----~----~------~----~------~--~---
