RESET BUTTON SCRIPT

[yvettew78]

Here is a screenshot of the calculator I am creating.
I have setup a script so when the PLUS sign located in Column A is pressed a new row is added under ROW 19 copying the formulas down.
I have also created a RESET button which resets all fields but it leaves the new rows that were added which stuffs up the cell references in the RESET script. I need to know if there is a way to fix this? Can I add a function which deletes any new rows that were added? Basically, it should revert back to three rows only).

Thank you!

 

[mumps]

It would help if you could post a copy of you current macro and also a screen shot (not a picture) using the XL2BB add-in.

 

[yvettew78]

It would help if you could post a copy of you current macro and also a screen shot (not a picture) using the XL2BB add-in.

Hi sorry, the script is as follows:-
function ClearCells() {
var sheet = SpreadsheetApp.getActive().getSheetByName('Calculator');
sheet.getRange('C10:C32').clearContent();
sheet.getRange('B10').setValue('Select Panel');
sheet.getRange('B11').setValue('Select Mounting Equipment');
sheet.getRange('B12').setValue('Select Inverter');
sheet.getRange('B13').setValue('Select Smart Meter');
sheet.getRange('B16').setValue('Select Extras');
sheet.getRange('B17').setValue('Select Extras');
sheet.getRange('B18').setValue('Select Extras');
sheet.getRange('E3').setValue('Select Payment Method');
sheet.getRange('E4').setValue('Select Property Type');
sheet.getRange('E5').setValue('Select Phase');
sheet.getRange('E44').setValue('$0.00');
sheet.getRange('E6').setValue('=IF(B10="Seraphim 330 Watt 120 Cell BLADE",C10*0.33,if(B10="Seraphim 400 Watt 120 Cell BLADE",C10*0.4,"0"))');
sheet.getRange('C10').setValue('=ROUNDUP(C7/6,0)');
sheet.getRange('C47').setValue('=ROUNDDOWN(E6*15.2,0)');
var cell = sheet.getRange("D10");
cell.setFormula("=IFERROR(VLOOKUP(B10,'Stock Items'!A3:B4,2,FALSE),0)");
var cell = sheet.getRange("D11");
cell.setFormula("=IFERROR(VLOOKUP(B11,'Stock Items'!A18:B19,2,FALSE),0)");
var cell = sheet.getRange("D12");
cell.setFormula("=IFERROR(VLOOKUP(B12,'Stock Items'!A7:B15,2,FALSE),0)");
var cell = sheet.getRange("D13");
cell.setFormula("=IFERROR(VLOOKUP(B13,'Stock Items'!A22:B25,2,FALSE),0)");
var cell = sheet.getRange("D16");
cell.setFormula("=IFERROR(VLOOKUP(B16,'Extras Pricing Schedule'!A2:B50,2,FALSE),0)");
var cell = sheet.getRange("D17");
cell.setFormula("=IFERROR(VLOOKUP(B17,'Extras Pricing Schedule'!A2:B28,2,FALSE),0)");
var cell = sheet.getRange("D18");
cell.setFormula("=IFERROR(VLOOKUP(B18,'Extras Pricing Schedule'!A2:B50,2,FALSE),0)");
var cell = sheet.getRange("D21");
cell.setFormula("=IFERROR(VLOOKUP(C10,'Installation Rates - Panels'!A4:B83,2,FALSE),0)");
var cell = sheet.getRange("D22");
cell.setFormula("=C22*'Installation Rates - Travel'!B4");
var cell = sheet.getRange("D23");
cell.setFormula("=C23*'Installation Rates - Travel'!B6");
var cell = sheet.getRange("D27");
cell.setFormula("=IF($E$40<1, 0,IF($E$40<15000, 25,IF($E$40<20000,50,IF($E$40<25000,125,IF($E$40<25001, 250)))))");
var cell = sheet.getRange("D29");
cell.setFormula("=SUM(E39-D32)*12%");
var cell = sheet.getRange("D30");
cell.setFormula("=SUM(E40-E39)/2");

}

Sorry I am new to this - what is XL2BB?

 

[yvettew78]

It would help if you could post a copy of you current macro and also a screen shot (not a picture) using the XL2BB add-in.

This is the add new row script

function addRow() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var range = ss.getActiveRange();
var R = range.getRowIndex();

sheet.insertRowsAfter(19, 1);
sheet.getRange('B20').setValue('Select Extras');
var cell = sheet.getRange("D20");
cell.setFormula("=IFERROR(VLOOKUP(B19,'Extras Pricing Schedule'!A2:B50,2,FALSE),0)");
var cell = sheet.getRange("E20");
cell.setFormula("=+C20*D20");


}

 

[mumps]

XL2BB is an add-in that you can install in Excel that allows you to post screenshots of your data (see the icon in the menu). The data can then be copied and pasted into Excel so that any possible solution can be tested. Unfortunately, you can't do this with a picture like the one you posted. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.

 

[mumps]

Since you are working with different sheets, it might be easier if you could upload a copy of your file to one of the sites mentioned in my previous post.