If/then

[Baziwan]

Hi all,

I wasn't sure how to Title this thread as the Solution to my problem might not be If/Then.
Ok, this one's very tricky to explain so I've attached an example file. My goal is to have excel adjust details of products received into a warehouse. If the codes match existing stock then the quantites and date are updated. If the product has no stock then the quantity, code & date are entered into the first bond columns. I've succeeded in doing that much.
Now for where I'm failing!
What I can't figure out is, if there is no matching code, but there is stock in the first bond columns then the details are entered in to the second bond columns. And again, if there is no matching code but there is stock in the first & second bond columns then the details are entered into the third bond columns.
Sounds Confusing, I know. But you'll see what I'm getting at when you take a look at the file and the code I've done so far.

Hope someone can help me out with this

Thanks in advance

[Baziwan]

Any ideas? Anyone.

[StephenR]

I've taken a look at your file and unfortunately I'm none the wiser. What are your inputs and outputs? What does the second sheet do?

[foxguy]

Hi Baziwan;

I can't duplicate your problem with your workbook.
I entered these values in:

Lemons CouldBe 8
Pears Maybe 7
Strawberries Mine 5
Strawberries Yours 10
And in 'Database'!E9 (Qty for BondCode 1) I put 1

When I ran Button2_Click() It put into Strawberries BondCode1 "5 Mine Today's date". It overwrote the previous quanity (1) with quanity "5 Mine" and ignored code "Strawberries Yours", "Pears Maybe" and "Lemons CouldBe". Exactly what I expected when I examined your code.
Here's the file before I hit "Confirm" Copy of Goods In Example.xls and here's the file after I hit "Confirm" Copy of Goods In Example 2.xls

If you can provide a workbook with data already entered into 'Goods In Bonded' ready to click "Confirm", I should be able to see your problem.

[Baziwan]

I've taken a look at your file and unfortunately I'm none the wiser. What are your inputs and outputs? What does the second sheet do?

Hi Stephen,

The second sheet is basically an adjustment sheet, to adjust the database. An example of how it will work is:
Using the example sheet, Say I get 150 Lemons arrive into bond. Bond either give it an existing code or assign a new one. If its an existing code, I want those details updated. So, I get 150 lemons under bond code LE659. When I enter that information into the goods-in form and click to confirm, the first bond columns on the database will read 450 in the qty, the bondcode will remain the same because it's an existing code and the date will be updated to today.
If however, I receive 150 lemons and they're assigned a new bondcode then the information will be entered into the second bond columns on the database. So if I receive 150 lemons and they're given the code 108, then when I click to confirm, the qty, code and date are added into the second bond columns. And so on....
I know it's a stinker, but I hope you can help me out.

[Baziwan]

There you go Foxguy,

Info in the goods in, ready to click confirm

[foxguy]

Hi Baziwan;

Sorry, still don't see the problem. Here's the file you just uploaded AFTER clicking "Confirm"Copy of Goods In Example-1.xls. I don't see anything wrong with the output. Tell me what's wrong with it, because I can't see it.

I did notice that it ignored the Bananas BN24 and the Plums BL827, which I expected, but my guess is that you didn't want them ignored.

[Baziwan]

That's exactly it Foxguy, I just don't know how to get it to enter the information into the next available columns if it doesn't match existing bondcode.

[foxguy]

Hi Baziwan;
I misunderstood the problem. I thought you were telling me what it did, not what you want it to do.
This looks pretty simple. Give me a few minutes.

[Baziwan]

On a completely unrelated subject. I'd just like to thank all you Forum Administrators,Guru's & Contributors. Every time I've hit a stumbling block trying to learn VBA, you guys come to the rescue. Not once have any of you acted condescendingly as some users on sites do, and there doesn't seem to be any problems that you guys can't solve. So I just want you to know that I think you're all top guys/girls.

[foxguy]

Hi Baziwan;

Here's you file back with the corrections Copy of Goods In Example Ver 2.xls
I put my correction between the '******* lines, and commented out your code that was unnecessary.

[Baziwan]

Thank you Foxguy, once again the forum has stepped up. Greatly appreciated bud.

[foxguy]

Thank you Foxguy, once again the forum has stepped up. Greatly appreciated bud.

You're welcome.