1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

To read an excel file and to make changes using VBA

Discussion in 'Business Applications' started by nynamyna, Aug 21, 2009.

Thread Status:
Not open for further replies.
Advertisement
  1. nynamyna

    nynamyna Thread Starter

    Joined:
    Aug 10, 2009
    Messages:
    28
    I have attached three excel files .....In the file called 'sampleBook1latest' I have a button called 'click here to clear a schedule' If I click that button and choose OR,start time and duration of a surgery then a schedule that is already created wil get cleared.....I have the total number of nurses and number of nurses used saved to the side of the sheet..... The total number of nurses at the side is read from another excel file called 'Input file for # of nurses'

    Now the problem is that if clear a schedule then the number of nurses used is not getting updated at the side.....if I clear a schedule then the number of nurses at the side should also get updated....for example(If I create a schedule with 3 nurses and if only a total of 3 nurses are available then..... the used number of nurses for that specific time at the side will get updated to 3 from 0 and if I clear that schedule then the used nurses for that time should again become 0)....

    The other problem is that if I schedule a surgery then all details of the surgery will be saved in another excel sheet called 'input file for patient details' so if I clear a schedule then all the details of that surgery saved in that excel sheet should also be cleared......

    Thank you
    Bharath
     

    Attached Files:

  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,840
    All the VBA that you need is in the sampleBook1latest, you should be able to work out how to do this part from that.
     
  3. nynamyna

    nynamyna Thread Starter

    Joined:
    Aug 10, 2009
    Messages:
    28
    can you give me a hint on which part of the code to make the changes.......
     
  4. nynamyna

    nynamyna Thread Starter

    Joined:
    Aug 10, 2009
    Messages:
    28
    Hi Tony

    I thought of a logic....Please help me to do it......here it is


    I tried to do some changes in userform2 ...to find a range of cells using OR and start time as input and to colour a range of cells in the 'input file for patient details' but it is not working...............I dont want to colour the range of cells but I tried it just to start with.....please check it and use it and help me to do the following

    In the userform 2 we give the input as start time , duration and OR number...we can take that as input and search for the OR number and the start time and read the corresponding 'number of nurses' column in the 'input file for patient details' and subtract that number of nurses value from that of the 'nurses used' which we have put at the sides......after reading it the entire row containing the specific start time and OR number should be deleted...Please help me doing it....

    Thanks
    Bharath
     

    Attached Files:

  5. nynamyna

    nynamyna Thread Starter

    Joined:
    Aug 10, 2009
    Messages:
    28
    Hi Tony

    You helped me to read the number of nurses from the excel sheet and update it at the sides....I used the same code to delete the number of nurses used when we clear a schedule........but it is not working.....Please have a look at my code (userform2) and help me correcting the mistake....



    Thanks
    Bharath
     

    Attached Files:

  6. nynamyna

    nynamyna Thread Starter

    Joined:
    Aug 10, 2009
    Messages:
    28
    Hi Tony

    I found the problem and corrected it....The problem was with the row count....

    Thanks for all the help you gave me
    Bharath
     
  7. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/854189

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice