Results 1 to 5 of 5

Thread: Excel vba format issue when trying to insert variable

  1. #1

    Excel vba format issue when trying to insert variable



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,

    I am trying to use variable where needed in the formula but it was not working or I did not know how insert it.
    Here below is the formula that I wish to pass.
    HTML Code:
        ActiveCell.FormulaR1C1 = _
            "=SUMIFS(R[5]C[36]:R[180]C[36],R[5]C[-2]:R[180]C[-2],""=141060"",R[5]C[1]:R[180]C[1],""=4500270213"")"
    I want to be able to change these values. On the other hand I tried to enter the formula differently than what you see. the wya it should be is:

    HTML Code:
    =SUMIFS("AO5":"AO300", "C5":"C300", "=141060", "F5":"F300", "=4500270213")
    I want the criteria to be variable so the "=141060" and the other one can change. Can any one help me write it properly?

    The first one above was created using the create macro. I could not figure out how these R and C got written this way.

    Regards,

    Chuck

  2. #2

    Solved Excel vba format issue when trying to insert variable

    Thanks to Hans from MSDN Forum for giving the answer.

    HTML Code:
    http://social.msdn.microsoft.com/Forums/office/en-US/663b3610-147c-4c4e-9eeb-39a95a142081/excel-2007-vba-excel-vba-format-issue-when-trying-to-insert-variable?forum=exceldev
    Works perfectly!

    Cheers!

    Chuck

  3. #3
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    773
    Articles
    0
    Excel Version
    2010
    Hi Chuck
    Based on what you've posted Im not sure what your trying to do so heres a few options:

    If your using your second example in a cell as a formula, then you should remove the quote marks from around the range addresses:
    = SUMIFS(AO5:AO300, C5:C300, "=141060", F5:F300, "=4500270213")
    If you want to pass the two criteria as variables, you place them into two cells, and replace the literals with the cell references.
    = SUMIFS(AO5:AO300, C5:C300, K1, F5:F300, L1)
    If you want to create a Custom function as below, you can pass the criteria as parameters:
    = AddForMe(141060, 4500270213).

    Place the following function code into a standaed VBA module to try it:

    Code:
    Public Function AddForMe(v1 As Double, v2 As Double) As Long
    Dim ao As Range, c As Range, f As Range
    Set ao = Range("AO:AO")
    Set c = Range("C:C")
    Set f = Range("F:F")
    AddForMe = Application.SumIfs(ao, c, v1, f, v2)
    End Function
    It would be quite easy to modify the code to pass the parameters as cell values, and if
    altered to a subroutine write the result total to a selected cell.
    Last edited by Hercules1946; 2013-12-14 at 08:09 PM.

  4. #4
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    773
    Articles
    0
    Excel Version
    2010
    Sorry Chuck
    Took a break for food, and didn't realise you had an answer. Im not able to copy the url you posted so Ill pass on looking at it

  5. #5
    Hi hercules1946,

    Thanks anyway for the tip. I must say this function can also be used. Appreciate your feedback.

    Cheers!

    Note: It is very cold today and snowy in Ottawa. Brrrrrrrrrr!

    Chuck

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •