If statements in VBA includes code to go to lastrow + 3 rows down

[dmekwunye]

I have a spreadsheet that has the following:

• Column AB has code to go to the lastrow + 3 rows and uses a SUMIF function; lastrow + 4 rows and uses a SUMIF function and lastrow + 5 rows and uses a SUMIF function:

Range("AB" & lastrow + 4).FormulaR1C1 = "=SUMIF(R11C1:R[-4]C[-27],""NQ"",R11C28:R[-4]C)"
Range("AB" & lastrow + 4).Select
Selection.Font.Size = 8
Selection.Font.Bold = True
Selection.NumberFormat = "_(* #,##0_ );_(* (#,##0);_(* ""0""_);_(@_)"
Range("AB" & lastrow + 5).FormulaR1C1 = "=SUMIF(R11C1:R[-5]C[-27],""PSU"",R11C28:R[-5]C)"
Range("AB" & lastrow + 5).Select
Selection.Font.Size = 8
Selection.Font.Bold = True
Selection.NumberFormat = "_(* #,##0_ );_(* (#,##0);_(* ""0""_);_(@_)"
Range("AB" & lastrow + 6).FormulaR1C1 = "=SUMIF(R11C1:R[-6]C[-27],""RSU"",R11C28:R[-6]C)"
Range("AB" & lastrow + 6).Select
Selection.Font.Size = 8
Selection.Font.Bold = True
Selection.NumberFormat = "_(* #,##0_ );_(* (#,##0);_(* ""0""_);_(@_)"
Range("AB" & lastrow + 7).FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)-R[-5]C"
Range("AB" & lastrow + 7).Select
Selection.Font.Size = 8
Selection.Font.Bold = True
Selection.NumberFormat = "_(* #,##0_ );_(* (#,##0);_(* ""0""_);_(@_)"
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDouble
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThick
End With

The code works just fine.

Next, I'm trying to enter the following function in column AG: =IF(A11="NQ",0,IF(A11="PSU",(AB11/AB3802*X11,IF(A11="RSU",(AB11/AB3803)*X11))))

The problem that I am having is that the cell reference of AB3802 and AB3803 will change depending on the data. It will always be the 5th row down from the last row based on Column A entries. The code for the formulas for column AB (above), works with any data - regardless of the number of rows in the spreadsheet. How can I get the formula for AG to work with spreadsheets of different number of rows?

Thanks,
De Anna

 

[jmacleary]

Hi there,
This bit of code should do what you want (change AG1 to whichever line you want the code in):

nearend = lastrow + 5
atend = lastrow + 6
Range("AG1").Formula = "=IF(A11=""NQ"",0,IF(A11=""PSU"",(AB11/AB" + Format(nearend) + "*X11,IF(A11=""RSU"",(AB11/AB" + Format(atend) + ")*X11))))"

 

[dmekwunye]

This worked perfectly. Thank's so much.