Posts by pradeep_atm

    Re: Auto Update a number as in accounting packages


    Regarding your query on how to go about...

    Assuming you have problem in having this code placed in a workbook module..follow the below steps:

    1. Have file saved as template(as suggested by Batman)
    2. Press Alt+F11 to go the VB editor
    3. In Insert menu slect module to insert a module
    4. Copy the code and paste it blindly into the module(font with blue are key words and as such you need not do anything on that)
    5. As instructed by Batman you should have created a text file before doing all the above or before running the code
    6. come to excel again pressing Alt+F11
    7. Press Alt + F8 and select the macro and press run..

    Hope this is what you are loking for..


    Re: Navigating Worksheets

    Hi All,

    I was looking for a solution for navigating through worksheets, while the "right-click" option is good, I don't want every time do the right click and do the rest of the job.

    Is there any short cut way by which I can get the list of sheets displayed(preferably by using key board short cut)..

    In short I want the "right-click" option to be assigned to a key board short cut, instead of a mouse click..

    hope I made it clear

    Please help.


    Re: List Compare


    Try the following steps for the first part of your question i.e to know the items which are not there in the other list(assuming List1 is where you want to check).

    1. Start by selecting the List1 range.
    2. Choose Format - Conditional Formatting
    3. In the Conditional Formatting dialog box, use the drop-down list to choose Formula is.
    4. Enter this formula:

    5. Click the Format button and specify the formatting to apply when the condition is true.

    6. Click OK

    This function counts the number of times a particular value appears in a range.

    This formating technique highlights the items which are not there in the other. If you are looking for taking all the entries, which are not there in the other list to a separate sheet, then do the following

    1. Insert two column just to the left your List 1 col.
    2. Have this UDF copied in the workbook module:

    Function Getcolor(Rn As Range) As Integer
    Dim d As Long
    d = Rn.Interior.ColorIndex
    Getcolor = d
    End Function

    3. Enter this udf in one column and enter the serial 1 to 40000 in the other column.

    4. Now sort on the basis of the column in which you have inputted UDF. All the items which are highlighted can be copied to a different sheet.

    5. Now again re sort by the column in which you have in putted serial nos. to get your original order and then delete the two columns which you have inserted for serial no & UDF.

    Hope this takes less time than what your code takes.


    Re: Text formatting


    You are a guru...

    Had I tried the Functions in a code I would have understood for myself(split, Ubound etc.,). Thanx for making me realize that.

    If I am not taking much of your time one more request:

    I need to sum the data as per below requirement:
    TXN category name
    1-59 Data Entry
    60-79 Funds Transfer
    80-84 Forex
    85-899 Trade

    Basically the code should sum the all the months data as per the above criteria by inserting a row just below it and name it as above(Data Entry etc.,). And all the Sum(total) lines to be copied and pasted in a new sheet...

    Since you already helped on this and will be more familiar with the problem.....that is why the request made to you.

    Enclosing the file for your reference.

    Kindly help.


    Re: Text formatting

    Hi TJ,

    Thanx for your code with the comments.

    I have gone through line by line of your comments...
    Please clarify the following on the same:

    1. What is meant zero based it which starts as 0,1,2...

    2. " 'The data has a trans, description and 12 months so for a single
    'word description the upper limit of the array is 13, for multi
    'word descriptions the uppler limit will be more than 13"

    ....this is what you said

    The Ubound is 13, is it because it is a Zero based array...

    3. The Redim Key word...what it stands for

    4. Is it not Ubound of the array is always greater than or equal to 14(TXN, Description, and 12 months...makes it 14 as minimum) or is it that 0-13 makes it 14..(since zero based array).

    One more small clarification...Is it possible to have the value data type(numeral type) as the output, instead of string data type, as I have to do some sum function on the these numbers. Other wise I have to use again value() worksheet function.

    Kindly clarify...

    Infact I have mentioned it in my earlier posting by editing it...but you have posted your reply much prior to my revised posting..


    Re: Text formatting


    Simply Excellent stuff.....

    It worked the way in which I wanted... :rock:

    One more small clarification...Is it possible to have the value data type(numeral type) as the output, instead of string data type, as I have to do some sum function on the these numbers. Other wise I have to use again value() worksheet function.

    And I am sorry for the inconvenience caused, due to the sample data I have given. I thought the sample data which I have chosen fairly explain the problem, but seems, it won't...

    And a small request..would be glad if could kindly let me understand how the code works...especially Ubound, split functions and the Redim key word....

    Really curious...... and looking forward to hear from you...

    John, While I thank you for the efforts you have taken to help me out, but am afraid the VB code you have suggested doesn't seem to work.


    Re: Text formatting

    Hi TJ,

    Thanx for your reply. Well it seems I have not made my self very clear. May be that I am not able to understand what you are trying to say.

    If you could kindly look at the Excel file attchment(with real data) and suggest a way, would be greatful.

    I am trying to get month wise data, whereas when I try text to column option or your Function, the data doesn't fitting under the respective columns(i.e months).


    Re: Text formatting

    Bill/ TJ,

    Thanx for taking your time in replying to this post. I tried your function and am afraid it gives the same results as that of Text to columns option under Data Menu in Excel with Space taken as the delimter.

    If you look at the excel which you have attached the data after splitting doesn't fall under specific columns. Again a manual adjustment required for placing the data under respective months.

    TJ, I have tried your function as well. It gives the same results as said above.

    I am attaching again the file for reference.

    Any help given these problems....


    Re: Text formatting

    Hi John,

    I have tried the same, but as I said earlier it gives all absurd results, in a way again I have to do manually to adjust the data to fit under respective columns. Considering the number of records it would be time consuimg if I go for manual editing.

    Any help on the formating.....may by way of code etc.,


    Re: Text formatting

    Hi John,

    Thanx for your reply. I tried through import wizard, but it doesn't give the necessary results. Infact it is same as Text to column option.

    Probably extracting numbers from the text option would help. But any clues as to how to use that...through formula or VB coding or...

    Please help.


    Hi All,

    Any ideas as to how to format the following text content in Excel. The report comes in notepad format and I need to have this in Excel. I tried search first before posting the query..

    060 TRANSFER 19752 19032 24609 22097 22940
    061 INCOMING MAIL TRANSFER 000 000 000 000 000
    062 INC. WIRE TRANSFER (Extn) 1864 1947 2351 2162 2268
    063 INC. CHECK TRANSFER (var) 000 000 000 000 000
    066 INC. CLEARING TRANSFER 1083 1314 1431 1593 1821

    The above is just a sample of the content, Infact the full data has info up to December. I tried using Text to columns option, but it is giving absurd results.

    Hope this makes some sense...

    I need to do some analysis on this by making totals month wise etc.,

    Any help would greatly appreciated...

    Thanx& Regards :thanx:

    Hi All,

    Could any one tell me, if it is possible to add any item to the Pop up menu which appears on the Right click of a Mouse. Say for instance want to add Paste special item to the pop up menu etc.,

    Any help would be greatly appreciated.

    Wishing you all a Happy & Prosperous New Year2005.

    Warm Regards
    Pradeep :thanx:

    Re: Combo boxes

    Hi Norie,

    Thanx for immediate reply. "It didn't work" meaning I tried some code by trying to populate the combo box run time, that didn't work.

    May be as you said Form control can't be added to Form, but both can be used in case Excel worksheet.

    Would like to undetstand the difference. In some thread Andy said, if a combo box is taken from control tool instead of Forms tool box, the font can be customized.

    Infact i wanted to post this question there itself, but that therad was already closed.

    Hope I made my self clear.


    Hi All,

    May I know what is the difference between Combo boxes taken from the Control tool box and that of from Forms tool Bar. Is that the latter combo box only works on UserForm.

    I took a combo box from Form Control tool box on Excel sheet, it didn't worked, whereas the one from Control tool box worked.

    Appreciate your reply.

    Thanx :thanx:

    Re: Error in file down loaded

    Roy and Jack in Uk,

    thanx for your reply. Well I thought it's a well known error message and never knew that there could be umpteen number of reasons.

    Thanx & Regards,
    Pradeep :thanx:

    Hi All,

    At times I encounter the following problem and the excel closes down. And one such instance was when I was changing the option in "changing combo" which I have down loaded from this site. Albeit later it didn't show up the problem, would like to understand the reason for such a problem.

    the error message is:
    "Excel.exe has generated errors and will be closed by Windows. You will need to restart the program. An error log is being created".

    Thanx for your help.:thanx: