# Blank cells replace zeros

• Good afternoon all.

I already have a formula in the cell to look for a "y" and replace with "From Stock"

IF(ISNUMBER(SEARCH("y",Order!G4)),"FROM H&S STOCK",Order!C4)

and I now what to add a formulas like this to get the cell if zero make blank

IF(Order!C4=0,"",(Order!C4))

Ive tried to get my head round getting these to work together can anyone help?

• It might be easier to see the workbook. Is it in the one with the UserForm?

I have an Inventory example which I was going to suggest could be incorporated into your workbook

• It might be easier to see the workbook. Is it in the one with the UserForm?

I have an Inventory example which I was going to suggest could be incorporated into your workbook

Good Morning Roy

Yes same workbook, I have tried using the show a zero in cells that have no value in excel options but when I use my email macro it still picks up the zeros so was hoping using this formula would work.

• There's no formulas in that tab in my example

Of course I started to play with this after. this is the code for getting a blank cell instead of zeros

=IF(Order!B4=0,"",(Order!B4))

• That formula works fine for me. What's the differencebetween Order and Purchase Order?

Your stock should be on one sheet then you can populate orders and invoices easily by using VLOOKUP based on stock code. All those different sheets for stock items will only make work for you.

• That formula works fine for me. What's the differencebetween Order and Purchase Order?

Your stock should be on one sheet then you can populate orders and invoices easily by using VLOOKUP based on stock code. All those different sheets for stock items will only make work for you.

So how did you combine it with the other formula? That's my problem

=IF(ISNUMBER(SEARCH("y",Order!G4)),"FROM H&S STOCK",Order!C4)

Ive used different sheets to make it easier for the vba userform so ill just have to find a way to do the stock how it is.

• I'm not really sure what your formula is doing.

I think your whole workbook could do with a re-design.

All the data on one sheet with an extra column for the type of product.

The Form could be made to filter the data for type.

Then your order forms would be easier to fill.

• Basically the formula is looking for Y in the stock column of the order sheet and if it finds a Y the Replace with FROM H&S STOCK.

It probably could do with redesigning but ive kinda stumbled myself through this far not to sure if ill know what to do with all the errors its going to cause lol

• Quote

It probably could do with redesigning but ive kinda stumbled myself through this far not to sure if ill know what to do with all the errors its going to cause lol

That's what we're here for.

• That's what we're here for.

I know Roy but I hate to keep asking for help

• It's not a problem. I'll see what I kind find in my old examples

Roy so ive now changed the sheets, can you have a look at the pick userform I now can workout how to get the combo box to see the products and list them under.

I think the same coding is used elsewhere so hopefully if you can point me in the right direction there I should hopefully be able to get thru the rest.

## Files

• REVAMP.xlsm

• I'll have a look. Thewcode will need re-writing.

Sorry for the late reply my work PC blew up and only just got a replacement installed yesterday.

Have you had a chance to have a look? if its to much trouble ill just carry on with these options

• Why do you need the pick userform? I would have thought VLOOKUP could populate the order form.

Also, you order forms have numerous unused rows with formulas, this is not a good idea.

• I've edited the code for PickForm so that when a product type is selected then that range of products is loaded to the ListBox

• Why do you need the pick userform? I would have thought VLOOKUP could populate the order form.

Also, you order forms have numerous unused rows with formulas, this is not a good idea.

Im not the only person that will be using this and was trying to keep away from the normal excel interface and hopefully make it easier to use maybe i have over thought thing?? Excel is not something ive trained in, Im a CAD user and CNC programmer so really only learning excel from what i can research on the internet.