Check unique parts, copy to another sheet, find cost for each part by date

[Stigmata101]

Hey guys

I have code to complete the first step, identifying unique parts and moving them to the new sheet, thanks to a member of the forum from another post of mine. I am leaving it out in the hopes that I will get a single solution.

Basically what I am trying to do is determine the trend of the cost of parts over a few days.

The first step is to collect all the unique part numbers and copy them to the Trend sheet. Then to find all the values for the previous 5 date entries, these dates are not sequential.

Once all the costs are in the table, I need to evaluate the trend, to determine if there is no change, flat trend, positive trend, or negative trend, increasing or decreasing cost values.

As I said, I have code to do the unique part move and I am using formulas to vlookup the costs and to do the trend analysis. The problem is that there is a large amount of data and the number of formulas kills the workbook.

Sample Workbook:

first half.xlsm

drive.google.com

Appreciate the assistance.
Stig

 

[Stigmata101]

No Worries Akuini, you helping me mate, super appreciated.

FYI, made a change to get the text and comma format:
This changed

'adjust sheet name
Sheets("Result").Activate
Range("A1").CurrentRegion.Offset(1).ClearContents
Range("A2").Resize(j, 6) = vb

Now this:

'adjust sheet name
Sheets("Result").Activate
Range("A:A").NumberFormat = "@"
Range("B:F").NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
Range("A1").CurrentRegion.Offset(1).ClearContents
Range("A2").Resize(j, 6) = vb

I tried to change the trend analysis but keep getting a type mismatch error.
What is tried:

If va(i, 3) > 0 and va(i, 3) >= va(i, 2) And va(i, 2) > va(i, 1) Then
        vb(i, 1) = "Positive"
    ElseIf va(i, 3) < 0 and va(i, 3) <= va(i, 2) And va(i, 2) < va(i, 1) Then
        vb(i, 1) = "Negative"
    ElseIf va(i, 3) = va(i, 2) And va(i, 2) = va(i, 1) Then
        vb(i, 1) = "Flat"
    End If

I think it has something to do with the va(i, 3) > 0, just not sure how to correct this.

The idea is that I only want to test for a positive trend if the latest cost is greater than 0, also, if the latest date and previous date cost are the same it can be deemed positive. Similar to a negative trend, just when less than 0.

I am going to keep trying and will update if I get it right.
Stig

 

[Akuini]

I tried to change the trend analysis but keep getting a type mismatch error.

I don't think it's because you changed the if statement.
In which line the error occured?
I tried your new if statement on your sample data, it worked fine.

 

[Stigmata101]

Hey Akuini

It seems to be something with the formatting that I did in the results tab. When I run it on the sample excel you shared, it works.

I am using the below to format the cells in the results tab:

Range("A:A").NumberFormat = "@"
Range("B:F").NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"

When I do this I get the type mismatch on the very first If statement

If va(i, 3) > 0 And va(i, 3) >= va(i, 2) And va(i, 2) > va(i, 1) Then

Stig

 

[Akuini]

I tried your code on the example, it works. So maybe something wrong with your actual data?
If you use General Format in B:F, does it work?

 

[Stigmata101]

So, it seems to not like it if a #N/A is returned in column B:F.

I did try general formatting and that made no difference. I found the missing data and am correcting it now.

Tested it now on my sample data and everything seems to be working correctly.

Going to give it a go with the full data set tomorrow and see what happens.

Off-topic, do you have a YouTube channel where you teach this method? It works really well and I would love to get a better understanding of it.

Stig

 

[Akuini]

No, I don't have a Youtube channel.
Basically, to speed up the process you need to minimize traffic between VBA and the worksheet. And usually I use array and Dictionary object to do that.
Here's a good source:

Excel VBA Array - The Complete Guide - Excel Macro Mastery

This post provides everything you need to know about the Excel VBA Array. Includes a quickVBA Array reference guide and 50+ VBA Array examples.

excelmacromastery.com

Excel VBA Dictionary - A Complete Guide - Excel Macro Mastery

This post contains everything you need to know about using the VBA Dictionary. There are tons of code examples including a real world application.

excelmacromastery.com