Posts by skamat

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

Re: Find Next Bill Number

thanks

Re: Find Next Bill Number

Quote from Batman;737509

That is what the Data Validation should do. It's basically not allowing you to enter that number because it's not one greater than the previous highest number. You can customise the error message if required.

Or are you saying that you still get the error message even if you enter a number that should be allowed?

Yes sir I get the error message even if I enter the number as shown.

Secondly when I enter the code in data validation and press enter it gives an error saying "The formula currently evaluates an error. Do you want to continue"

Please see the attached file.

Re: Find Next Bill Number

I have done as you told and Cell F2 shows the next bill number
I have also entered the formula data validation

But when I enter the bill number it gives an error
The Value You entered is not valid
A user has restricted values that can be entered into this cell.

I make the bills randomly as and when I get information.

Please see the attached file

You can see the bill numbers mentioned randomly.

I am having many more parties but shown only few here, I would like to create a formula in which Cell F2 would display the next bill number

For Eg. in the Cell F2 it shows next bill number as 350

When I create a new bill in S. No. 1 as 350, the Cell F2 should automatically change to 351 as next bill number.

BUT when F2 shows 350 and I enter 353 or any other number besides 350 it should give an error.

Re: Run Time Error 429

Sorry for late reply

After lot of search on the internet I finally got the solution which I wish to share here in case others face the same problem.

To solve th error I Reregistered the dlls