Excel 2007 VBA Case Function Hi I have many IF in a formula and I want to change

Question

Excel 2007 VBA Case Function

Hi

I have many IF in a formula and I want to change this for a function in vba

The results will be different message in a cell depending of the selection in 3 different columns.

The message will advise the user for an error, still in Pending or if everything is ok and close

Column A will be the formula with the function

The function should verify the Column W (Answ) , Column AA (Close) Column AH (Send)

Do I use the right codes to do this?

I tried this below and get an error "Function not valid" when I tried to use the function

This example use row2

W = Where the agent select the Answer

AA= The date he close if the case is close

AH = Only need when the user select "accept-$" in col W. Otherwise it's blank

Col A (Where the message will appear) the function is : = message (W2,AA2,AH2)

VBA

Function Message(Answ, Clse, Send)

    Select Case Answ And Clse And Send

    Case Answ = "Accept-$" And Clse <> "" And Send = ""
                Message = "Please Send"

    Case Answ = "Accept-$" And Clse <> "" And Send <> ""
                Message = "Close$"

    Case Answ = "Accept-P" And Clse <> "" And Send = ""

                Message = "CloseP"

    Case Answ = "Accept-P" And Clse = "" And Send = ""

                Message = "Pending"

    Case Answ = "" And Clse = "" And Send = ""

               Message = "Pending"

    Case Answ = "Refuse" And Clse <> "" And Send = ""

                Message = "ClseR"

End Select
End Function

I have others Cases to add.

Please if you have the solution can you add comments lines? I want to understand the logic of it.

Thanks everyone

Explanation / Answer

>>>Hi,w46

*Try it this way. Note also you have no Case Else so if none of the cases is TRUE then the function returns zero. I put a case else in but didn't assign anything to it.

Also it seems you cant use Message as your function name so changed to Msg.....

=msg(W2,AA2,AH2)

Function Msg(Answ, Clse, Send) As String
Select Case True
Case (Answ = "Accept-$" And Clse <> "" And Send = "")
    Msg = "Please Send"
Case (Answ = "Accept-$" And Clse <> "" And Send <> "")
    Msg = "Close$"
Case (Answ = "Accept-P" And Clse <> "" And Send = "")
    Msg = "CloseP"
Case (Answ = "Accept-P" And Clse = "" And Send = "")
    Msg = "Pending"
Case (Answ = "" And Clse = "" And Send = "")
    Msg = "Pending"
Case (Answ = "Refuse" And Clse <> "" And Send = "")
    Msg = "ClseR"
Case Else

End Select
End Function

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.