VBE - Need Serious help

  • Thread starter Thread starter dixonpeter
  • Start date Start date
D

dixonpeter

First of all, I'd like to say how great this board is, and how well it
has given me help over the last 6 or 7 months with building small
programs with vbe.

My problem is this, I need a program that is far in advance of my own
skills, its basically a stock control program for steel coils.

The Fields are:

Material - ComboBox1
:- This will choose from categories CR, GALV, IZ, Zintec, ALuminised
etc.

Width - Textbox1
:- This is where you type in the width you are looking for or wanting
to add.

Gauge - Textbox2
:- This is where you type in a gauge (Something like 0.95 or 1.15)
and it shows in two labels the weight and the quantity from the
workbook, I presum this is some kind of look up function, and I will
probably need a commandbutton to initilise this function. If it cant
find the size it comes up with zero.

The last textbox is where you type in a quantity.

Then there are two CommandButtons, one you can add that quantity, and
one you can take away that quantity.

I've attached a picture, because I think it might help more than what I
have explained, if anyone can help me out, it would significantly
reduce my workload in stock control and I would be much appreciative.

Thanks

Peter


+----------------------------------------------------------------+
| Attachment filename: example.bmp |
|Download attachment: http://www.excelforum.com/attachment.php?postid=357345|
+----------------------------------------------------------------+
 
Hi

Why do you need VBA for such task at all. Something exprompt:

Create a sheet Materials, which all used materials listed in column A
(Material)
Create a dynamic named range
Material=OFFSET(Materials!$A$2,,,COUNTIF(Materials!$A:$A,"<>")-1,1)
(A1 contains header)

Create a sheet Articles with a row for every article (an entry with unique
combination of material, gauge and width)
Article, Material, Gauge, Width, Stock

You have to decide the possible maximale number of different articles. Let
for our example it be 999
Select range B2:B999, and then Data.Validation.List, and set Source=Material
Select range A2:A999, and then Data.Validation.Custom, and set
Formula=COUNTIF($A:$A,"="&A2)<2
Fill the table with available articles (all except column Stock)
Create named ranges
Article=OFFSET(Articles!$A$2,,,COUNTIF(Articles!$A:$A,"<>")-1,1)
ArticleTable=OFFSET(Articles!$A$2,,,COUNTIF(Articles!$A:$A,"<>")-1,4)

Create a sheet Transactions with columns
Article, In/Out, Date, Amount, Material, Gauge, Width
You have again to decide the possible max number of transactions for the
period, your workbook is meant for. Let it be 4999
Select range A2:A5000, and then Data.Validation.List, and set Source=Article
Select range B2:B5000, and then Data.Validation.List, and set Source=In,Out
Into E2 enter the formula
=IF(ISERROR(VLOOKUP(A2,ArticleTable,2,FALSE)),"",VLOOKUP(A2,ArticleTable,2,F
ALSE))
Into F2 enter the formula
=IF(ISERROR(VLOOKUP(A2,ArticleTable,3,FALSE)),"",VLOOKUP(A2;ArticleTable,3,F
ALSE))
Into G2 enter the formula
=IF(ISERROR(VLOOKUP(A2,ArticleTable,4,FALSE)),"",VLOOKUP(A2,ArticleTable,4,F
ALSE))
Select E2:G2, and copy to range E2:G4999
Enter starting amounts for every article available, with In/Out as In, and
startdate as Date
For every article coming in afterwards, insert a new row, with In/Out as In,
and incoming date as Date
For every article sent out insert a new row, with In/Out as out, and sending
date as Date
Create named ranges
MoveArticle=OFFSET(Transactions!$A$2,,,COUNTIF(Transactions!$A:$A,"<>")-1,1)
MoveInOut=OFFSET(Transactions!$B$2,,,COUNTIF(Transactions!$A:$A,"<>")-1,1)
MoveAmount=OFFSET(Transactions!$D$2,,,COUNTIF(Transactions!$A:$A,"<>")-1,1)

On sheet Articles, into cell E2 enter the formula
=IF(A2="","",SUMPRODUCT((MoveArticle=A2)*(MoveInOut="In")*(MoveAmount))-SUMP
RODUCT((MoveArticle=A2)*(MoveInOut="Out")*(MoveAmount)))
and copy to range E2:E999
As result, for every article the current amount in stock is diaplayed

It's all for start. But you can add various report sheets, where you can
select various prameters (material or gauge or width or start/end dates for
period) and where predesigned reports are then displayed. And protecting the
formulas from accidental changes would be wise, or using autofilter feature
on Articles or Transactions sheets.
 
Back
Top