Posts by loknath

    Dear All
    I could not find a way to make the following formula to be executed through a loop OR other means in Excel 2007 VBA. There are 50 records and the records starts from row 9 till row 58 in the Sheet 20.
    Any suggestions for the code would be highly appreciated.
    Thanks.



    Re: Index,Match Large


    Hi Pike, Thanks for the information. But I Have no problem finding the 1st and 2nd Largest. I mean the 1st and 2nd largest can be a same value, eg- 50 in my example. But my problem is how to trace the names from name item if the 1st and 2nd largest values are tied.

    I trying to insert multiple column values from many sheets (eg- sheet 1, sheet2, sheet3 which have the same format) to another master sheet (sheet 7) using a command button in sheet1, sheet2, sheet3 to push the data of these sheets to the cells in sheet7 so that everytime the command button in each sheet is clicked, the data is updated in the cells in Sheet7.I don't want to create multiple master sheets like Sheet7 by using excel formulae. Instead I am trying to achieve this using excel VBA. My codes are given below, but while running the code, the error pops up saying the "Compile error:procedure too large". How can I shorten the codes to achieve the task.

    [/QUOTE][/CODE]

    Re: Printing Specific Range based on Cell Value


    Hi Ger,
    Thanks for your comment and the help. To be specific I have a worksheet (Sheet3) in which there are about 100 Pages . In each page in Sheet3, the Intersection of Row3,Column16 has a cell value (Name of a person). This name is linked to name in sheet1. So, what i want is that I want to print only those Pages in Sheet3 where the Intersection of Row3,Column16 (In Each Page) has a cell value (Name of a person). I don't want to Print from Page 32 and below as the Cell Row3,Column16 in Page 47 is Blank. I want to Use a UserForm to Select the Names from the list and Print Pages for all the Names or Specific Names based on Selection from the ComboBox.
    Thanks
    Loknath

    I have pulled the data into Sheet3 from Sheet1 using excel formulae.Sheet1 Contains Provision for Names in Range A2:A52 (Provision fior 50 Names). Sheet3 has about 50 pages Formated depending on the data in Sheet1. Every Page in Sheet3 has same format with 29 Rows and 26 Columns. If there are only 30 Names in Sheet1, then the data in Sheet3 for the balance 20 are blank or zero.
    Every page in Sheet3 has a link formula to abstract the name from Sheet1.Intersection of Row 3 and Column 16 in Every Pagen in Sheet 3 has the Name abstracted from Sheet1. If Sheet1 has no Name, this Row3 and Column 16 has Zero Value.
    My problem is How can I use VBA in Excel 2007 to Print only those Ranges (Row1 to Column16) in Every Page in Sheet3 In Which the Cell Value (Row3,Column16) is Not Zero. How can I use a Command Button in Sheet3 to accomplish this task.
    loknath

    Dear all,
    I am having aproblem with IF function to show either 0 or Blank or Value in a cell based on condition.
    Example:
    If A2=0 or A2="" or A2=15, AND A4=0 or A4="" or A4=60,then I want to show the result in A5 based on the entry made in A2 and A4 as follows:
    IF(A2=0,0,IF(A2="","",IF(A4=0,0,IF(A4="","",(A2+A4*.3))))
    User may enter 0 or leave blank or enter any value in A2 and A4.
    I would appreciate if someone could help me on this formula or any other related formulae to get the result.


    Thank you in advance.


    Lok

    Hello all,
    I have 5 sheets. I have used the following formula to show Blank instead of Zero in Sheet2.
    =IF('Sheet1'!A5+'Sheet1'!A7)="0","",('Sheet1'!A5+'Sheet1'!A7))
    Where, in Sheet1, A5 is a value entered and A7=A5+A6


    But with this formula I get the value '0.00' in Cell B3 in Sheet2.


    Could you help me as to how to get Blank cell instead of Zero in Sheet2 in Cell B3


    Thank you all for your kind help.

    Re: Userid And Password To Access Worksheets


    Hi Krish,
    Thanks a lot for the hardwork. It works great. Is there a possibility that "user login form" is shown directly at the start of excel and donot show ANY Sheets. The sheets will be only shown after the correct login.


    Thank you for the help

    Re: Userid And Password To Access Worksheets


    Helo Krish,
    It is wonderful to provide sheet access to the users. But, when i tried to protect the Workbook Structure with a password and saved the workbook, the error pops up" Run time error'1004'. Unable to set the visible property of the worksheet class. Have you any idea to solve this. Because users may delete the sheets if workbook is not protected in my case.


    Thanks.


    Lok