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

#### Stigmata101

##### New Member
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:

Appreciate the assistance.
Stig

#### Stigmata101

##### New Member
No Worries Akuini, you helping me mate, super appreciated.

FYI, made a change to get the text and comma format:
This changed
VBA Code:
``````'adjust sheet name
Sheets("Result").Activate
Range("A1").CurrentRegion.Offset(1).ClearContents
Range("A2").Resize(j, 6) = vb``````

Now this:
Code:
``````'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:
Code:
``````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

### 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.

#### Akuini

##### Well-known Member
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

##### New Member
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:
VBA Code:
``````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
Code:
``If va(i, 3) > 0 And va(i, 3) >= va(i, 2) And va(i, 2) > va(i, 1) Then``

Stig

#### Akuini

##### Well-known Member
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

##### New Member
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

##### Well-known Member
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:

Replies
8
Views
105
Replies
4
Views
623
Replies
8
Views
287
Replies
1
Views
367
Replies
35
Views
542

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,396
Messages
5,769,841
Members
425,574
Latest member
grimeslisa

### 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.

### Which adblocker are you using?

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

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