T
trobinson
Hello,
I need help fast! I need to be able to insert a Formula into rows that
contain certain text. I then need to be able to copy all this formula
across 15 columns and down to the end of the active workbook for each
row that contains this criteria. I want the formula to automatically
update to the correct cell reference. My formula is rather lengthy but
it works perfectly in a 'normal' spreadsheet without running a macro.
This is what I have:
Dim intx as Integer
Dim lngrow as long
ActiveCell.SpecialCells(xlLastCell).Select
lngrow = ActiveCell.Row 'lastcell in spreadsheet
For intx = 1 To lngrow
Cells(intx, 1).Select
If InStr(1, ActiveCell.Value, " Target Renewal") > 0
Then
Range(Cells(intx, 6), Cells(intx, 20)).Select
Selection.Formula =
"=IF(AND(OR($D$2=30,$D$2=60),D15=""aprent"")," -
",IF(OR(E19=$B$2*(1-($D$2/30)),E19=$B$2*(1-(($D$2-30)/30)),E19=$B$2*(1-(($D$2-60)/30)),E19=$B$2*(1-(($D$2-90)/30)),E19=$B$2),$B$2,IF((F15+$D$2)<30,$B$2*(1-($D$2/30)),IF(OR(D15=""aprent"",F15>0),"
- ",IF((F15+E15+$D$2)<60,$B$2*(1-(($D$2-30)/30)),IF(D19=""," -
",IF((D15+E15+F15+$D$2)<90,$B$2*(1-(($D$2-60)/30))," - ")))))))"
End If
Next
Any help is appreciated. Thanks!
I need help fast! I need to be able to insert a Formula into rows that
contain certain text. I then need to be able to copy all this formula
across 15 columns and down to the end of the active workbook for each
row that contains this criteria. I want the formula to automatically
update to the correct cell reference. My formula is rather lengthy but
it works perfectly in a 'normal' spreadsheet without running a macro.
This is what I have:
Dim intx as Integer
Dim lngrow as long
ActiveCell.SpecialCells(xlLastCell).Select
lngrow = ActiveCell.Row 'lastcell in spreadsheet
For intx = 1 To lngrow
Cells(intx, 1).Select
If InStr(1, ActiveCell.Value, " Target Renewal") > 0
Then
Range(Cells(intx, 6), Cells(intx, 20)).Select
Selection.Formula =
"=IF(AND(OR($D$2=30,$D$2=60),D15=""aprent"")," -
",IF(OR(E19=$B$2*(1-($D$2/30)),E19=$B$2*(1-(($D$2-30)/30)),E19=$B$2*(1-(($D$2-60)/30)),E19=$B$2*(1-(($D$2-90)/30)),E19=$B$2),$B$2,IF((F15+$D$2)<30,$B$2*(1-($D$2/30)),IF(OR(D15=""aprent"",F15>0),"
- ",IF((F15+E15+$D$2)<60,$B$2*(1-(($D$2-30)/30)),IF(D19=""," -
",IF((D15+E15+F15+$D$2)<90,$B$2*(1-(($D$2-60)/30))," - ")))))))"
End If
Next
Any help is appreciated. Thanks!