Match data, Copy, preform calulation, loop

[steve1262]

I posted this on http://www.ozgrid.com/forum/showthread.php?t=56824 2 days ago, bumped it twice, got lots of looks but no help. I am very "weak" at coding but I am trying to learn it and complete this project as well.

The attachment has a notes tab with what I am trying to do. But it is basically a inventory/material ordering process, with a "Size" and a "Quantity" which are variable.

Any help or direction would be most helpful ! and VERY thankful.

Thank you

 

[venkat1926]

I somewhat understand . but it is aprojet too many ifs and very much tailored. shall try may take a day or two. No guarantee I shall be succesful toyour satisfction

venkat

 

[venkat1926]

i have done the firstr part.(step A) i would like to confirm whethre it is ok
safely park your file some where so tht you do not lose your data
there are two macros
run the macro test A and see what happens to sheet partsneeded.
now run the macro UNDO
this macro undoes the result of the macro
again test with TESTA
cojfirm ok or not ok
if not ok the line the problem occurs and the error message.

add anaotgher worksheet call it "tempo"
(without quotes)
there you coopy the entries of partneeded in shee tempo a1

similarly copy entreis in parts available in sheet tempo A10 onards

and entries in sheet partsflled in sheet tempo from A18

this will help you undo the result of the macro testA and retet it.

I am giving the sheet tempo below
Below the sheet tempo I have given the macros.

PartsSampleData3.xls
	A	B	C	D	E	F
1	partsneeded
2
3	MetalType	Gauge	Width	Length	Psize	Pqty
4	GalvalumeKynar	28	48	120	13	1000
5	ColdRolledCopper	24	48	120	22	2000
6	ColdRolledCopper	24	48	120	9	3000
7
8	partsavailable
9
10	MetalType	Gauge	Width	Length	Psize	QTYP
11	GalvalumeKynar	28	48	120	13	300
12	ColdRolledCopper	24	48	120	22	5000
13	ColdRolledCopper	24	48	120	12	1000
14
15
16	partsfilled
17
18	MetalType	Gauge	Width	Length	Psize	Pqty
19	GalvalumeKynar	28	48	120	13	300
20	ColdRolledCopper	24	48	120	22	2000
21	ColdRolledCopper	24	48	120	9	1000
22	ColdRolledCopper	24	48	120	9	2000
tempo

The macros are

Sub undo()
Worksheets("partsneeded").Cells.Clear
Worksheets("partsavailable").Cells.Clear
Worksheets("partsfilled").Cells.Clear
Worksheets("tempo").Activate
Range("a3").CurrentRegion.Copy Worksheets("partsneeded").Range("a1")
Range("a10").CurrentRegion.Copy Worksheets("partsavailable").Range("a1")
Range("a18").CurrentRegion.Copy Worksheets("partsfilled").Range("a1")

End Sub

Sub testA()
Dim sha, shn, shf As Worksheet
Dim j, k, m As Integer
Set sha = Worksheets("partsavailable")
Set shn = Worksheets("partsneeded")
Set shf = Worksheets("partsfilled")
shn.Activate
m = shn.Cells(Rows.Count, "a").End(xlUp).Row
For j = 2 To m
For k = 1 To 4
If k > 4 Then GoTo line1
If j > m Then GoTo line2
If shn.Cells(j, k) = sha.Cells(j, k) Then
End If
Next k
If shn.Cells(j, "e") > 0 And sha.Cells(j, "e") > 0 Then
If shn.Cells(j, "e") = sha.Cells(j, "e") Then
If shn.Cells(j, "f") > sha.Cells(j, "f") Then
shn.Cells(j, "f") = shn.Cells(j, "f") - sha.Cells(j, "f")
Else
shn.Cells(j, "f") = 0
End If
Else
shn.Cells(j, "f") = 0
End If
End If
line1:
Next j
line2:
MsgBox "testA is over"
End Sub

 

[venkat1926]

Perhaps I have not understood.
Your step A
THEN DO THIS

QUOTE
STEP 4. SUBTRACT worksheet "PartsNeeded" (Row with match) Column F from worksheet "PartsAvailable" Column F (Same row as above)
STEP 5. PLACE a "0" in worksheet "PartsNeeded" (Row with match) Column F
UNQUOTE
Step 4 after subtracting where does this value go
Step 5. Under what conditions is 0 to be placed. in “needed” column F

Please clarify

Similar clarifications may be required for other step B,C and D

 

[venkat1926]

IGNORE PEVIOUS MESSAGS

are you still intrested in the solution

I have done some macro for sheets Partsneeded and partsavalable. I am doing for the third sheet partsfilled.

if you are still interested send me an email I want to send the file back to you for some clarifications.