Hello,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I am having problems building the foundation of my project, any help would be greatly appreciated.<o></o>
<o></o>
All this procedure needs to do is sum the setup hours and runtime hours for each machine.<o></o>
<o></o>
Ideally, I would like to utilize an array holding the machines unique name along with its corresponding setup rate and runtime rate.<o></o>
<o></o>
In the end I would like the procedure to find the following:<o></o>
cost = setup rate * setup hours + runtime rate * runtime hours<o></o>
Each machine has a specific setup rate, and runtime rate.<o></o>
<o></o>
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></o>
<o></o>
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></o>
<o></o>
<o></o>
<?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" oreferrelative="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><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><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></o>
<o></o>
<o></o>
<o></o>
Thanks in advance for the help,<o></o>
<o></o>
jkr<o></o>
<o> </o>
<o></o>
I am having problems building the foundation of my project, any help would be greatly appreciated.<o></o>
<o></o>
All this procedure needs to do is sum the setup hours and runtime hours for each machine.<o></o>
<o></o>
Ideally, I would like to utilize an array holding the machines unique name along with its corresponding setup rate and runtime rate.<o></o>
<o></o>
In the end I would like the procedure to find the following:<o></o>
cost = setup rate * setup hours + runtime rate * runtime hours<o></o>
Each machine has a specific setup rate, and runtime rate.<o></o>
<o></o>
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></o>
<o></o>
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></o>
<o></o>
<o></o>
<?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" oreferrelative="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><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><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></o>
<o></o>
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></o>
Thanks in advance for the help,<o></o>
<o></o>
jkr<o></o>
<o> </o>