[Solved] VBA: How to bring a value over to function()

  • hello,


    I having a problem with my Macro under the function().


    First a user will enter a value(month1).


    Then i declare the value as: monthsum = month1 & " total"


    Then the value(monthsum) will bring into the function() but the monthsum is read as empty.


    Can i able to bring the Value(monthsum) in the function()??


    How can i type my code to enable it?


    Thank YOU.....


    CODE:


    Sub Macro1()


    month1 = InputBox("Enter NEW QUARTER(3 MONTH), EXAMPLE: Oct", "Current Month")


    If month1 = "Jan" Then
    pmonth = "Dec"
    End If


    If month1 = "Feb" Then
    pmonth = "Jan"
    End If


    ''''*SOME CODE HERE*''''


    monthsum = month1 & " total"


    X = formula(monthsum)


    ''''*SOME CODE HERE*''''



    End Sub


    Function formula()


    'the monthsum is empty. How come??


    Cells.Find(What:=monthsum, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
    xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False).Activate

    columnsum = ActiveCell.Column
    rowsum = ActiveCell.Row


    ''''*SOME CODE HERE*''''


    End Function


    Thank YOU......

  • I Think you need something like


    Function formula(monthsum as String)


    and in the code for the function you need


    formula = something


    at the end


    That way formula returns a value


    HTH

    There are three types of people in this world.
    Those who can count and those who can't.

  • Oh Neale was do fast for me...


    Okay, here is just my suggestion for cheking entry and how to get previous month.


  • Thank you Colo and Neale...


    Colo the code to find the preious month also help me.. i also looking for this code..
    thank you...


    I have another way to solve this problem... i forget to add X at the function()... now can work....


    anyway thank you very much..


    CODE:


    Sub Macro1()


    month1 = InputBox("Enter NEW QUARTER(3 MONTH), EXAMPLE: Oct", "Current Month")


    If month1 = "Jan" Then
    pmonth = "Dec"
    End If


    If month1 = "Feb" Then
    pmonth = "Jan"
    End If


    ''''*SOME CODE HERE*''''


    monthsum = month1 & " total"


    X = formula(monthsum)


    ''''*SOME CODE HERE*''''



    End Sub


    Function formula(X)


    Cells.Find(What:=X, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
    xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False).Activate


    columnsum = ActiveCell.Column
    rowsum = ActiveCell.Row


    ''''*SOME CODE HERE*''''


    End Function

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!