Match/Filter cell contents to an array

jkr

New Member
Joined
Feb 28, 2010
Messages
12
Hello,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
I am having problems building the foundation of my project, any help would be greatly appreciated.<o:p></o:p>
<o:p></o:p>
All this procedure needs to do is sum the setup hours and runtime hours for each machine.<o:p></o:p>
<o:p></o:p>
Ideally, I would like to utilize an array holding the machines unique name along with its corresponding setup rate and runtime rate.<o:p></o:p>
<o:p></o:p>
In the end I would like the procedure to find the following:<o:p></o:p>
cost = setup rate * setup hours + runtime rate * runtime hours<o:p></o:p>
Each machine has a specific setup rate, and runtime rate.<o:p></o:p>
<o:p></o:p>
I have tried many different approaches, match, vlookup, etc, without success, and have become frustrated. This is just my latest effort to get a match using the filter method. I was planning to use an if statement inside the for loop, if length result(i) > 0, then something...<o:p></o:p>
<o:p></o:p>
I've gotten several types of errors over my attempts, range, object, etc. Any solution could be used. I do not think my approach here is best...<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" o:preferrelative="t" o:spt="75" coordsize="21600,21600"><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><v:path o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></v:path><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype><v:shape style="WIDTH: 24pt; HEIGHT: 24pt" id=_x0000_i1025 alt="" type="#_x0000_t75"></v:shape><o:p></o:p>
<o:p></o:p>
Code:
 <o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Option Explicit<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] <o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Sub timecatagory()<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] <o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Dim lastrow As Long
Dim i As Integer
Dim searchvalue As String
Dim wkcntr(0 To 19) As String
Dim results() As Variant
Dim rngwkcntr As Range
Dim arresults As Range<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] <o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]lastrow = ActiveSheet.Range("A65536").End(xlUp).Row<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] <o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]wkcntr(0) = "MACHINE10"
wkcntr(1) = "MACHINE11"
wkcntr(2) = "MACHINE12"
wkcntr(3) = "MACHINE13"
wkcntr(4) = "MACHINE14"
wkcntr(5) = "MACHINE15"
wkcntr(6) = "MACHINE16"
wkcntr(7) = "MACHINE17"
wkcntr(8) = "MACHINE18"
wkcntr(9) = "MACHINE19"
wkcntr(10) = "MACHINE20"
wkcntr(11) = "MACHINE21"
wkcntr(12) = "MACHINE22"
wkcntr(13) = "MACHINE23"
wkcntr(14) = "MACHINE24"
wkcntr(15) = "MACHINE25"
wkcntr(16) = "MACHINE26"
wkcntr(17) = "MACHINE27"
wkcntr(18) = "MACHINE28"
wkcntr(19) = "MACHINE29"<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] <o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Set rngwkcntr = ActiveSheet.Range(Cells(2, 1), Cells(2, lastrow))
Set arresults = ActiveSheet.Range(Cells(10, 1), Cells(10, lastrow))<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] <o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]For i = 1 To lastrow<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] <o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]    results(i) = Filter(wkcntr, Range(Cells(1 + i, 1)), , vbTextCompare)
    
Next i<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] <o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]arresults = WorksheetFunction.Transpose(results)<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] <o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]End Sub<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]
<o:p></o:p>

<o:p></o:p>
Thanks in advance for the help,<o:p></o:p>
<o:p></o:p>
jkr<o:p></o:p>
<o:p> </o:p>
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Watch MrExcel Video

Forum statistics

Threads
1,130,112
Messages
5,640,174
Members
417,130
Latest member
Darion2021

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