Match data, Copy, preform calulation, loop

steve1262

Board Regular
Joined
Aug 19, 2006
Messages
126
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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
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

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
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
ABCDEF
1partsneeded
2
3MetalTypeGaugeWidthLengthPsizePqty
4GalvalumeKynar2848120131000
5ColdRolledCopper2448120222000
6ColdRolledCopper244812093000
7
8partsavailable
9
10MetalTypeGaugeWidthLengthPsizeQTYP
11GalvalumeKynar284812013300
12ColdRolledCopper2448120225000
13ColdRolledCopper2448120121000
14
15
16partsfilled
17
18MetalTypeGaugeWidthLengthPsizePqty
19GalvalumeKynar284812013300
20ColdRolledCopper2448120222000
21ColdRolledCopper244812091000
22ColdRolledCopper244812092000
tempo








The macros are

Code:
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
Code:
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

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
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

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
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.
 

Forum statistics

Threads
1,136,354
Messages
5,675,292
Members
419,559
Latest member
BraytonM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top