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

LTT · Sep 26th 2003

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......

Neale · Sep 26th 2003

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

Colo · Sep 26th 2003

Hi,

All you have to do is PASS the value of monthsum as an argument as follows.

Code

Function formula(ByVal monthsum As String)

Colo · Sep 26th 2003

Oh Neale was do fast for me...

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

Code

Sub Macro1()
    Dim i As Long, blnFlg As Boolean, dat As Date
    
ReInput:
    month1 = InputBox("Enter NEW QUARTER(3 MONTH), EXAMPLE: Oct", "Current Month")
    month1 = Application.WorksheetFunction.Proper(month1)


    ' Check for entry
    For i = 1 To 12
        dat = DateSerial(Year(Date), i, 1)
        If month1 = Format(dat, "mmm") Then
            blnFlg = True
            Exit For
        End If
    Next


    If Not blnFlg Then
        MsgBox "No such Month, please confirm your entry."
        GoTo ReInput
    End If


    'Get pre month
    pmonth = Format(dat - 1, "mmm")
    
    monthsum = month1 & " total"
    X = formula(monthsum)
    ''''*SOME CODE HERE*''''


End Sub

Display More

LTT · Sep 26th 2003

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

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

Participate now!

Share