CODEWITHSUNDEEP
excelvbaobjectworksheet-function
Sudio
Sudio

Reputation: 155

Object required for WorksheetFunction

I've got a user form with 2 drop down lists, and I'm using the values of these lists in a formula to display the results to the user. I'm having issues storing the formula result in a variable and displaying that in a message box. I have the bit of code below, but it throws an "Object required" error. I have seen some examples of formulas that put the result in a cell, but I don't need my value displayed anywhere except for the message box. Any ideas on what is causing my code to fail?

Dim selBranch As String
Dim selSkillTitle As String
Dim result As Variant

selBranch = frmQuery.cboBranch.Value
selSkillTitle = frmQuery.cboSkillTitle.Value

result = WorksheetFunction.AverageIfs(skill_prof, branch, selBranch, skill_title, selSkillTitle)

MsgBox "Query Result" & vbNewLine & "--------------" & vbNewLine & _
    "Branch: " & branch & vbNewLine & _
    "Skill Level: " & skillTitle & vbNewLine & vbNewLine & _
    "Average Skill Proficiency: " & CStr(result), vbInformation

skill_prof, branch, and skill_title are all named ranges in my workbook.

Upvotes: 0

Views: 617

Answers (1)

JvdV
JvdV

Reputation: 75840

WorksheetFunction.AverageIfs(....) would expect some range variables instead of non-declared empty variables.

You mentioned they are named ranges, therefor you need the right syntax to refer to them, possibly doing so in multiple ways, here is one:

Dim skill_prof as range: Set skill_prof = Worksheets("WhicheverSheet").Range("skill_prof")
'Etc

The above will make sure you can use the same formula in your code. Just make sure these named ranges are equal sized =)

Upvotes: 1

Related Questions