Hi Hemant,
I tested the code and made few changes as needed but still it says "For/Next
loop not Initialized", where are we wrong in below code, please advice.
*Sub test()
j = 1
While j <= 65000
For i = 2 To Worksheets("Replace_Values").Cells(65000, 1).End(xlUp).Row
If Worksheets(1).Cells(j, 2) Like Sheets("Replace_Values").Cells(i, 1) Then
Worksheets(1).Cells(j, 3) = Sheets("Replace_Values").Cells(i, 2)
Else
Worksheets(1).Cells(j, 2) = Worksheets("Replace_Values").Cells(i, 3)
End If
i = i + 1
Next i
j = j + 1
Wend
End Sub*
Cheers!!
ANISH
On Tue, Oct 27, 2009 at 2:15 PM, Anish Shrivastava <anish....@gmail.com>wrote:
> *Hi Hemant,*
>
> Will surely try it and let you know the results. If it works then this
> would be Great.
> Thank you very much.
>
> *Hi Dave,*
>
> Thanks mate for explaining the use of "Goto100", I must say as a learner
> it is a quite useful info for me.
>
> *Hi Deepak,*
>
> Thanks for suggesting this, I will try writing select Case (As I am a
> beginner in VBA). I learnt 3 ways to accomplish dis task after posting this
> mail. :)
>
> Cheers!!
> ANISH
>
> On Mon, Oct 26, 2009 at 3:12 PM, Hemant Hegde
> <hemantbales...@gmail.com>wrote:
>
>> 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
-~----------~----~----~----~------~----~------~--~---
