Variable as lookup value in Match Function in macro

[tknight]

I new to programing in vba but have other programing experience. The one thing that keeps getting me is the formatting that vba uses.

Is there a way to use a variable as the lookup value within the match function in a macro? I have tracked my problem down to the match function not being able to handle a variable as the lookup value.

Application.WorksheetFunction.Match(Variable, .....

I'm not sure if there is a need for more code to answer this question and this is at one of the last steps in a code that has several inputs and calculations.

Thanks,
T

 

[RCBricker]

I new to programing in vba but have other programing experience. The one thing that keeps getting me is the formatting that vba uses.

Is there a way to use a variable as the lookup value within the match function in a macro? I have tracked my problem down to the match function not being able to handle a variable as the lookup value.

Application.WorksheetFunction.Match(Variable, .....

I'm not sure if there is a need for more code to answer this question and this is at one of the last steps in a code that has several inputs and calculations.

Thanks,
T

This would be easier if you posted the code using tags

However, you should just be able to type your variable into the function as you have stated in your example.

the syntax is .worksheet.function.match(arg1,arg2,arg3...etc). each argument should be able to be replaced with a variable.

rich

 

[Jonmo1]

Yes you can use variables.

Why don't you post the match function you have that doesn't use variables, and describe which part of the function you want to replace with a variable.

 

[tknight]

So what I'm trying to do is compare the number of employees needed at a production station with the output of the station. My original code had the number of units per day hard coded as the lookup value to test if the rest of my code was working but when I changed it to a variable I received the error "Method 'Match' of object 'WorksheetFunction' failed". After some research I'm pretty sure it is not possible to use a variable as the lookup value when using the Match function in vba. I have since rewritten my code to be more robust and shorter using a do until loop.

 

[Jonmo1]

My original code had the number of units per day hard coded as the lookup

Can you post that code that worked with hard coded values?

 

[tknight]

Units=Application.WorksheetFunction.Index(Sheets("Balanced Production Line").Range("A" & i & ":A" & ), Application.WorksheetFuntion.Match(50, Sheets("Balanced Production Line").Range("K" & i & ":K" & ii), 1) + 1))

 

[Jonmo1]

OK, I see no problem with replacing 50 with a variable like

Dim MyVariable As Long
MyVariable = 50
Units=Application.WorksheetFunction.Index(Sheets("Balanced Production  Line").Range("A" & i & ":A" &[COLOR=#ff0000] ii[/COLOR]),  Application.WorksheetFuntion.Match([COLOR=#ff0000]MyVariable[/COLOR], Sheets("Balanced Production  Line").Range("K" & i & ":K" & ii), 1) + 1))

I also noticed your ii was missing for the end row number in the first range (maybe that is actually the problem?)

 

[tknight]

It was there, I typed that from memory so I must have been going a little fast.

 

[Jonmo1]

ok, so post the code you have now that is trying to use a variable.
And include the code that assigns a value to that variable.

 

[tknight]

The sheet that the data is on has multiple stations with 15 employees each so this code will go through all the stations and find the value for units that matches the required units per day. So I created my own Index/Match of sorts:

While i < LastRow + 2


OverviewRow = Application.WorksheetFunction.Match(Sheets("Balanced Production Line").Range("M" & j), Sheets("Overview").Range("C13:C35"), 0)
StationPercentage = Application.WorksheetFunction.Index(Sheets("Overview").Range("D13:D35"), OverviewRow, 1)


Units = Sheets("Balanced Production Line").Range("K" & i)
Count = 1
StationUnits = i


If UnitsPerDay = 0 Then
Sheets("Balanced Production Line").Range("N" & j).Value = 0
ElseIf Units > UnitsPerDay Then
Sheets("Balanced Production Line").Range("N" & j).Value = Count * StationPercentage
Else


Do Until Units > UnitsPerDay


Units = Sheets("Balanced Production Line").Range("K" & StationUnits)
Sheets("Balanced Production Line").Range("N" & j).Value = Count * StationPercentage
Count = Count + 1
If StationUnits = 1 Then
StationUnits = i + 1
Else
StationUnits = StationUnits + 1
End If


Loop
End If


Sheets("Overview").Range("E" & jj).Value = Sheets("Balanced Production Line").Range("M" & j)
Sheets("Overview").Range("F" & jj).Value = Sheets("Balanced Production Line").Range("N" & j)


i = i + 17
ii = ii + 17
j = j + 1
jj = jj + 1
Wend