none
Create range from variable RRS feed

  • Question

  •         .Range("G43:G195").Formula = "=$C43-$D43" 
            .Range("H43:H195").Formula = "=IF($G43<0,IFERROR(ROUND($G43/$E43,4),-2),IFERROR(ROUND($G43/$F43,4),2))" 
            .Range("I43:I195").Formula = "=IF(ABS($H43)>1,""Fail"",""Pass"")" 

    This is the current code I have however I have a "find" portion earlier where it searches for a specific cell which becomes a variable named start. I would like to make the range part ("G43:G195") reference this start location say to the right 5 columns and down 1 row to be the starting cell of the range and the end cell be 153 rows below the starting cell.

    This is a sample of what i tried for the first equation, also I am not able to get the formula to change based on the start cell either.

    .Range(Cells(StartV.Row + 1, StartV.Column + 5), Cells(StartV.Row + 153, StartV.Column + 5)).Formula = "=$C43-$D43"

    Any help is appreciated, thank you.

    Thursday, March 5, 2020 12:07 PM

All replies

  • I don't know what you want the formula to do, but perhaps

        StartV.Offset(1, 5).Resize(153).FormulaR1C1 = "=RC[-4]-RC[-3]"
        StartV.Offset(1, 6).Resize(153).FormulaR1C1 = "=IF(RC[-1]<0,IFERROR(ROUND(RC[-1]/RC[-3],4),-2),IFERROR(ROUND(RC[-1]/RC[-2],4),2))"
        StartV.Offset(1, 7).Resize(153).FormulaR1C1 = "=IF(ABS(RC[-1])>1,""Fail"",""Pass"")"


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, March 5, 2020 2:26 PM