Posts by skamat

WOW, thanks works very well

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 a lot

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 )

Private Sub Capital(ByVal Target As Range)
Dim Z As Long
Dim xVal As String
On Error Resume Next
If Intersect(Target, Range("D:D")) Is Nothing Then Exit Sub
Application.EnableEvents = False
For Z = 1 To Target.Count
If Target(Z).Value > 0 Then
Target(Z).Value = UCase(Target(Z).Value)
End If
Next
Application.EnableEvents = True
End Sub

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.

Re: Sum values in formula CONCATENATE

working great, Thanks

I have a text stating concatenate with randombetween ( As in the example )

I would like to sum the two numbers, without using "mid" or "left" or "right" formula, because randbetween can generate any 1,2, 3 or 4 digit numbers and using mid, right or left will give error.

Is there any other way

Re: Copy data if row matches

great thanks

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

Re: Sum gives #Value! in Cell

Thank you very much Sir

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

[/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

Got it , separate if and end if works better than the code you provided.

Please let me know how to put a vba code that asks to select a printer.

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