locked
Minimum Function with variable number of cells referenced RRS feed

  • Question

  • Hi

    I am fairly new to VBA code so bare with me but I am trying to write VBA code for a minimum function that will contain a variable number of cell references.

    I can calculate the location of all the cells but not sure how to get the FormulaR1C1 Property to add/remove the cell references accordingly. Is there another property that will allow me to do this?

    Any suggestions?

    Regards

    Toby

    Wednesday, December 2, 2020 9:59 AM

All replies

  • Are you using the MIN funtion to find the minimum in a number of non contiguous cells or in a straight range of cells?

    The following code is as recorded to find the minimum number in a range of non contiguous cells.

    ActiveCell.FormulaR1C1 = "=MIN(RC[-3],R[3]C[-3],R[8]C[-3],R[13]C[-3],R[18]C[-3],R[22]C[-3],R[27]C[-3])"

    If you select the cell containing the formula on the worksheet then in the formula Bar you will see the formula as follows.

    =MIN(A2,A5,A10,A15,A20,A24,A29)

    The VBA code can then be edited as follows using the formula as a string that is assigned to the cell

    ActiveCell.Formula = "=MIN(A2,A5,A10,A15,A20,A24,A29)"

    Referring to above line:

    The R1C1 style is not used, the equals sign still follows Formula and there is another equals sign within the string.

    The cell references can also be assigned to a single string variable as follows.

    strCells = "A2,A5,A10,A15,A20,A24,A29"

    The variable can then be used to concatenate a string as follows to create the formula in the ActiveCell

    ActiveCell.Formula = "=MIN(" & strCells & ")"

    An array can be used to hold the cell references and then used as follows to create the string for the formula.  First create the array.

    arrCellRefs = Array("A2", "A5", "A10", "A15", "A20", "A24", "A29")

    Then use as follows to concatenate and create the string for the formula

    ActiveCell.Formula = "=MIN(" & Join(arrCellRefs, ",") & ")"

    The Join function above separates the elements of the array into a comma delimited string for the formula. therefore if you build an array with the required cell references then the array can be used as above in the formula.

    If this does not assist you then please reply with more information of exactly what you are attempting to achieve. Include examples of what ranges you are finding the minimum.


    Regards, OssieMac

    Friday, December 4, 2020 11:10 AM