PLEASE HELP VBA, Match Data, Copy, Subtract, Loop HELP !!

[steve1262]

I am really stuck on a long project that I have been working on. This is the next to the last step; I need to complete this project. In VBA I am looking to do the following.

IF

data in Sheet1 any row, columns A,B,C is = to data Sheet 2, any row columns A,B,C,

AND
Sheet1 (matched row) column D is, => than Sheet2 (matched row) column D

AND

Sheet1 (matched row) column E is, > than 0

AND

Sheet2 (matched row) column E is, > than 0

THEN DO THIS

STEP 1

COPY Sheet1 (match) columns A,B,C to Sheet3 next available row columns A,B,C

STEP 2

1. “In Sheet1” SUBTRACT Sheet2 (match) column D from Sheet1 (match) column D
2. COPY Sheet2 (match) column D to Sheet3 column D (same row as above)

STEP 3

IF

Sheet1 (match) Column E is =< Sheet2 (match) Column E

Then

1. Copy Sheet1 (match) Column E to Sheet3 Column E (same row as above)
2. Subtract Sheet1 (match) Column E from Sheet2 Column E
3. Delete entire row Sheet1 (match)

Elseif

Sheet1 (match) Column E is > Sheet2 (match) Column E

Then

1. Subtract Sheet2 (match) Column E from Sheet1 (match) Column E
2. Copy Sheet2 (match) Column E to Sheet3 (same row as above)
3. Delete entire row Sheet2 (match)

STEP 4


LOOP until no data is found.

To help speed up the loop (if needed) the maximum number of rows will never be more than 150, so you could just loop the first 150 rows if it is easier to write the code.

I can post some sample data if needed, column A is TEXT, the rest of the data is numeric with possible of 3 decimal places.

Like this

A = “Copper”
B = 48.375
C= 120
D= 14.375
E= 2500

I know this is A LOT of code to write. Any help would really be appreciated. I am learning how to write in VBA but this is way beyond my level.



Steve

 

[jindon]

OK
I'll take a good look at your explanation..

By the way, how about my quesiotion 1?

1) Step1 then step2, update data with Step1 then step2

or

2) Step2, if not satisfy, Step1?

 

[steve1262]

Jindon,

I think I know what you are asking, and Step1, Step2 might be the way to go.

I think I have this figured out mathematically. To make it easier to read I am going to shorten the names of the worksheets.

A = “Available”
N = “Needed”
F = “Filled”

A.E = Worksheet “Available”, Column “E”, Row match
N.F = Worksheet “Needed”, Column “F”, Row match

Remember Column “E” is Size (can be divided to make more quantity) and Column “F” is Quantity.

So I guess this would be STEP 1

After the code identifies a match analyze the data to see if you have an enough to fill the order 100% or just partial.

I guess this would be STEP 2


IF A.F >= N.F Then GOTO, Step A (Order can be filled 100%)

IF A.F < N.F Then GOTO, Step B (Analyze to see if order can be filled 100% or Partial)

STEP A

A.E – N.E = x
x = Updated A.E (value)
A.F – N.F = y
y = Updated A.F (value)
‘ insert more code here to show part filled 100% (removed from “Needed” and add to “Filled”)

STEP B

‘find out what the max value of A.F

(RoundDown,0) A.E / N.E = p
p * A.F = m ‘(maximum amount available)

‘Compare to amount needed to see if order can be 100% filled or not

IF m >= N.F (Part can be 100% filled) GOTO STEP B1

IF m < N.F (Part can NOT be filled 100%) GOTO STEP B2

STEP B1

‘Determine how much A.E is needed to Fill the order and reset A.E Value

(RoundUp,0) N.F / A.F = d ‘(number of whole units needed)

‘Reset the A.E Value

d * N.E = j

A.E – j = Updated A.E (value)

‘ Insert more code here to show part filled

STEP B2

‘Show how much N.F can be filled and reset A.E Value

m = F.F (value)

‘Update A.E value

(RoundUp,0) N.F / A.F = d ‘(number of whole units needed)

d * N.E = j

A.E – j = Updated A.E (value)

‘Insert more code here to show part filled

I think I have covered all of the possible scenarios. I’ll bet you can do this in a much more efficient manner, with fewer steps. Please let me know if you have any questions.

Thank you so much.

Steve

 

[steve1262]

Error in math

Jindon,

I have a question about math at this step.

STEP A

A.E – N.E = x
x = Updated A.E (value)
A.F – N.F = y
y = Updated A.F (value)
‘ insert more code here to show part filled 100% (removed from “Needed” and add to “Filled”)

Below each line is one part or A.F
The length of the line is the width or A.E

So lets say A.F = 5 and A.E = 10, it would look like this.

__________
__________
__________
__________
__________

and we have a part that needs to be filled N.F = 10 N.F = 2.5
after the parts were removed it would look like this

_____
_____
_____
_____
_____

1/2 of the width removed from every part.

But to optimize the use of the parts it really needs to look like this.

_____
___________
___________

2 whole parts and 1/2 of one part.

How can we do this mathematically?

I am going to think about it and post back, if you have any ideas let me know.

Thanks

Steve

 

[jindon]

Steve

Looks like you are talking about FIFO(First-In First-Out) algorithm?

Or

Left_Over (x) = A.E * A.F - N.E * N.F
If x > A.F Then A.F = A.F , A.E = x / A.F
Else A.F = x , A.E = 1

??