Posts by skamat

    I was looking for a method that mentions the date if it is a working day

    I have a list of 12 Dates as below

    20-01-23

    23-01-23

    24-01-23

    25-01-23

    27-01-23

    30-01-23

    31-01-23

    01-02-23

    02-02-23

    03-02-23

    06-02-23

    07-02-23


    Please note that I have not mentioned Saturday and Sunday as it is a Holiday

    I have not mentioned 26-01-23 as it is holiday here in India


    Now starting from last date mentioned ie. 07-02-23 I wanted to create a formula that mentions 1 day earlier than today, For Eg. today is 08-02-23, and 1 day early it should mention 07-02-23, if it is a working day else the previous date should be mentioned. ( For. Eg. If Previous Day is Sunday it should Take Friday as Saturday is a holiday )


    Then go to 06-02-23 that is 1 day early from 07-02-23


    Now see 03-02-23, here 4th and 5th is Saturday and Sunday so it is not mentioned so it took date as 03-02-23.


    I need this as I have to change the date every day for getting the latest price for last 12 days.

    Thanks Works Very Well in my Sample Work Sheet.


    But when I am trying to implement it in my main worksheet nothing happens.

    This may be because I already have a VBA code in my main work sheet, and both Private Sub is not working.

    This Code I have written so anything I type is converted to Capital Letters.


    Here is the Code ( I do not know how to highlight the VBA code so writing it normally )


    I want to create a Macro in such a way that whenever a date is changed or multiple date entered in anywhere in Column C3 onwards and then enter key is pressed the page should refresh automatically. Pressing enter anywhere else in the sheet nothing should happen.


    This is because I have created a Macro and every time I change the Date I have to Press Data-Refresh

    Actually the borders are not applying as I would like to have
    As mentioned in my query the borders should apply from column A, ( even if there is no text )
    Same for the Row below the Alphabet C


    I have attached sheet for easy reference in my first post, wherein there are 2 sheets present one without borders and another exactly how I wish to have

    I think this has been asked before, I had searched the forum but that solution did not help me.


    Attached is the sheet wherein Sheet1 is the data I have
    I would like a VBA code so that a border is applied from column A to the right end till the last column. ( The end result is given in sheet2)


    My data can change to any number of columns or rows, and accordingly border should be applied


    I tried VBA code selecting column B where text is present, and apply borders from column A to last column, But it did not work.

    I dont know how this is possible.


    Please see the attached sheet.


    Sheet1 is the main sheet with all the data


    Sheet2 Cell E5 has a selection with data validation.


    Depending on the data selection we make I would like the data to be displayed as below.


    I have given all the data below each other for easy understanding, but in actual I want the data to be present only in ROW 8


    I would appreciate a formula and not the VBA code

    Please see attached book


    I have made a formula when all numbers are present the total is calculated in cell C25, but when no numbers are present the cell C25 displays #Value! error.


    I would like the cell to be blank when no numbers are present and calculate the total if numbers are mentioned in any cell between C3:C20

    Re: Print if cell contains value


    Sir the option to select the Option in the Print Dialogue to 'Selection' is not active.


    and even though I changed the VB code to


    Code
    [/COLOR]=If Sheets("rent").Range("f5").Value <> "" Then


    it still asks to select printer for all pages that is to be printed.



    Re: Print if cell contains value


    Sir, thanks for the print dialog box.
    Although it asks for selection of printer there are 2 glitches


    1. It asks for selection of printer for each page to be printed, whereas I would like it to ask only once.


    2. It prints sheet named Rent rather than Sheet named Rent Bills.


    Sir as far as I am comfortable with If and End if is because it gives the output perfectly well as I require rather than using your code.


    Please see the attached file wherein your Code has been kept and the code with Endif and If.


    If there is any mistake please let me know.

    Re: Print if cell contains value


    Please see the modified copy of what I exactly require.


    Sheet1 named Rent contains all the details of the party.
    Sheet2 named Rent bills contains all the bills made.


    Currently When I click command button print it prints only pages 1 and 2 from the sheet named Rent Bills, and not all what I require.


    Now what I exactly require is


    The print out should be taken only when Cells in F contains a value.


    Here F6 contains the value so it should print page 1 and 2
    Here F7 contains the value so it should print page 3 and 4
    Here F8 does not contains any value so it skip
    Here F9 contains the value so it should print page 7 and 8


    and so on


    Before printing a dialog box should appear asking to select the printer only once and not for all printouts.


    Hope I am now clear.

    Re: Print if cell contains value


    Please give me a days time and I will prepare the exact worksheet and upload my requirements.
    I have also prepared a code but it does not work the way I want, will upload the code also.

    Re: Print if cell contains value


    What I mean to say is in Sheet1 D5 contains value 56
    D6 contains 78 and so on and D9 does not contain any value.


    Now when print command button is pressed on sheet1


    Then if there is any value in D6 then is should print page number 1 and 2 given in sheet2


    Then D7 should print pages 3 and 4 in sheet2


    Since there is no value in D9 nothing should be printed.


    Before printing It should ask for printer selection also.


    Attaching the sample excel sheet.


    Hope am clear

    Please see the attached sheet wherein would like to print certain page number if the cell contains a value


    For. Eg.
    In sheet1, D5 contains a number so it should print page 1 to 2 on sheet2
    In Sheet2, D6 contains a number so it should print page 3 to 4 on sheet2
    and so on
    D9 does not contain a number or is 0 so nothing should be printed
    D10 contains data so it should print page 6 to 8 on sheet2


    When printing it should also ask for a selection of printer only once at the start and not for each page to what it needs to print