Rounding specific cell values to the next multiple of 10 and using to set Chart

Question

Rounding specific cell values to the next multiple of 10 and using to set Chart value axis limits

Hi there,

I am using a Formula Array in cells H2 and H3 in Sheet2 (say) but using VBA coding.

What I need to do with these values is round them both up to the next multiple of 10. I do not want them just to round up/down to the nearest 10. Also, the value in H3 is a negative so technically 'rounding down' to the next negative multiple of 10 (if that makes sense).

Ie. If H2 = 73 then I want this to be rounded up to 80 and this value held in cell I2

    If H3 = -54 then I want this to be rounded to -60 and this value held in cell I3 (supposedly this might mean timing -54 by -1 then rounding up and then timing the 60 by -1 again) not entirely sure if its easier the way I first described.

Anyway, just want to know how this would be done using VBA coding. Range2 I already understand and have sorted the code out for. Array formulas for cells H2 and H3 below:

        ActiveSheet.Range("H2").FormulaArray = "=MAX(IF(Range2>0, Range2)"

        ActiveSheet.Range("H3").FormulaArray = "=MAX(IF(Range2<0, Range2)"

        MaxPos = ActiveSheet.Range("H2").Value

        MaxNeg = ActiveSheet.Range("H3").Value

Explanation / Answer

*^Y&ou cannot use a VBA range *)(Range2) within a worksheet formula. The following code should work:"|

    Dim MaxPos As Double
    Dim MaxNeg As Double
    Dim last2 As Long
    last2 = Cells(Rows.Count, col).End(xlUp).Row
    Range("H2").FormulaArray = "=MAX(IF(D1:D" & last2 & ">0, D1:D" & last2 & "))"
    Range("H3").FormulaArray = "=MIN(IF(D1:D" & last2 & "<0, D1:D" & last2 & "))"
    Range("I2").Formula = "=ROUNDUP(H2,-1)"
    Range("I3").Formula = "=ROUNDUP(H3,-1)"
    MaxPos = Range("I2").Value
    MaxNeg = Range("I3").Value

Related Questions

Navigate

• Browse (All)
• Browse R
• Subjects

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