# Create range from variable • ### 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