Posts by msn_manju

    Hi All,

    I have a workbook which has sales data in one worksheet and inventory data in one worksheet. There around 50 workbooks I get each week one for each customer. The column headers are same for sales and inventory in all the sheets. All the 50 files are in the same folder.

    Is there a way to put the sales data of all customers in one worksheet and inventory in one worksheet. Currently I am doing it manually.


    Re: Inputbox options

    Thanks for the reply Dave.

    I want to remove the "Cancel" option because, when the user clicks "Cancel" it returns a false. This is giving me runtime error in the code.

    I have attached the file which has the code


    • Book3.xls

      (23.55 kB, downloaded 81 times, last: )

    Hi All,

    I have an inputbox in my code. By default the inputbox is giving the options "OK" and "Cancel". Is there a way where I can have only "OK" option in my inputbox.



    There are around fifteen pivot tables in my workbook. Is there a macro which would refresh all pivots in a workbook. Also is it possible to make the macro dynamic so that the macro would work even if the pivot table names are changed.



    I have data validation in cells A1:A50 which allows to select values from a list. There are about 50 items in the list that can be selected. Is it possible to have the user select an item only once i.e., if an attempt is made to select an item thats already selected it show an error message.

    Thanks for the help

    Re: Removing non-english characters

    Hi Dave,

    Thanks for your reply. If there is a more dynamic method it would be helpful, because Ž and Ÿ are only some of the examples and there are many characters like that and each day there might be new characters like that.

    Thanks again


    I have the data in text format which I have to import it to Excel. The problem is that it has around 120k rows of data. The method I am doing it is by splitting the text file into two halves and importing it into two worksheets.

    Is there a way of importing the data without splitting the file (its okay if the output is in two worksheets).

    Thanks in advance

    Re: Using variable in vlookup

    I have two xls file. File one has different sheet tabs, i want to run a macro to put formula of vlookup in column B from the another xls file which has same # of tab as of First file. So i want a variable in vlookup i.e "sheet_name".

    So discription of vlookup is
    Selection.FormulaR1C1 = _
    "=IF(B2<>"""",IF(ISERROR(VLOOKUP(B2,[abc.xls]" & sheet_name & " '!$A:$C',2,FALSE)),0,VLOOKUP(B2," & sheet_name & " '!$A:$C',2,FALSE)))"

    thank u

    Re: Using variable in vlookup

    1. sheet_name = ActiveSheet.Name
    2. Range("B:B").Select
    3. Selection.FormulaR1C1 = _
    4. "=IF(RC[-1]<>"""",IF(ISERROR(VLOOKUP(RC[-1],[THQ.xls]" & sheet_name & " '!$A:$C',2,FALSE)),0,VLOOKUP(RC[-1]," & sheet_name & " '!$A:$C',2,FALSE)))"


    I am trying to pass a variable in vlookup. I am able to pass variable in same sheet, however if i want variable from different sheet its not working code as below Plz help.

    1. Selection.FormulaR1C1 = _
    2. "=VLOOKUP(RC[-1],[ABC.xls]" & sheet_name & "!R1C1:R207C3,2,FALSE)"

    Thank u