CODE REVIEW: copy paste + look up

[dunnobe]

Hey guys,

This code works as intended, but I am wondering if someone could give me some advice on how to code faster and cleaner.

Purpose: verifying if our purchase orders were sent out.
Method: I summarize 3 worksheets with data in the worksheet 'Overview'.
I first create the headers, then copy paste 5 columns and then fill in 2 more columns with data using look ups.

Please Login or Register  to view this content.

*Uploaded v4 with some tweaks and first feedback.

[dunnobe]

Some additional information. I have the for each/do until loop from another guy on the forum who gave me some advice.
I basically recycled it and adapted it to my needs.

In the first part, I look up a PO-number (r.value) in a worksheet that has this PO-number multiple times and check if one of these PO-numbers has status = "1".
In the second part, I count how many times r.value is in a worksheet and count how many of these are deleted ("L"). -> If the # lines = # deleted lines, the entire order has been deleted (= varResult).

[Sintek]

I am very certain that someone will be able to simplify and speed up the process...However, a sample upload file depicting your exact setup will be required...

[dunnobe]

Cool, I will after my break!

I might be doing some unnecessary looping in the last part of the code. I have to look up the value only 1 time and count # lines and # deleted lines and then find the next r.value.

This seems to give the same result and cuts down the running time of the code in half!

Please Login or Register  to view this content.

[mohadin]

Hi dunnobe
There is another loop not needed in fact
for the Headers try some thing like:

Please Login or Register  to view this content.

[dunnobe]

@mohadin:

I guess it has to be like this as the first value of an array is 0 and thus your code would be ... , Cells(1, 0) = headers

Please Login or Register  to view this content.

[Sintek]

Please Login or Register  to view this content.

[Sintek]

'! Thread not allowing me to edit post...............

Please Login or Register  to view this content.

[dunnobe]

Hey sintek,

Thanks.
The result is a bit off, but it's interesting to see how you do things diferrently than me .
I can learn from it.

Br,

dunnobe

[Sintek]

The result is a bit off

Can you clarify by an upload with expected result...Your initial upload which had the results has been replaced with another file...

[dunnobe]

Hey sintek,

There might be a mistake in the latter part of the code:

Please Login or Register  to view this content.

A value could occur multiple times in worksheet 'NAST'.
F.e. Orders 4522256126, 4522256189, 4522256257 occur multiple times in that table, with both a corresponding value 0 and 1. Considering value 1 exists for these numbers, it should return "OK".
Does your code only returns a result for the first time it finds the value?

[Sintek]

Oh, i see what you mean...So if any of the rows containing that number has a 1 in Column U then it must be ok...
Declare cnt as long and then this should work...

Please Login or Register  to view this content.

[dunnobe]

Thank man! +1 reputation

The result seems to be the same as my macro for this small amount of data, but faster.

I'll have a look later on to see what I can learn from your code.

[Sintek]

Pleasure...Tx for rep +