Forum Discussion
Recording script in Excel; formula not dragging down, only values
I'm trying to create a script in Excel by recording. Everything works great except when I run it, the cell I dragged down to copy a formula down gives the result of the value from the first cell and ...
Please post your script so we can see what is going on. Also, show us the formula you intended to drag down. Finally, I would "send a frown" about this (Excel desktop: in the ribbon, click Help, Feedback, Excel on-line: click "Give feedback to Microsoft" in the bottom-right corner)
- function main(workbook: ExcelScript.Workbook) {// Add a new worksheetlet sheet2 = workbook.addWorksheet();// Set range A1:G2 on sheet2sheet2.getRange("A1:G2").setFormulasLocal([["First Visit","Company","Name","Title","Email","Phone","Client Feedback"],["=Sheet1!L2","=Sheet1!H2","=LEFT(PROPER(Sheet1!F2&\" \"&PROPER(Sheet1!G2)),20)","Owner","email address removed for privacy reasons","xxx-xxx-1270","=Sheet1!E2"]]);// Set format for range A:A on sheet2sheet2.getRange("A:A").setNumberFormatLocal("[$-en-US]mmmm d, yyyy;@");// Auto fit the columns of all cells on sheet2sheet2.getRange().getFormat().autofitColumns();// Auto fill rangesheet2.getRange("A2:G2").autoFill("A2:G400", ExcelScript.AutoFillType.fillDefault);// Set print area for sheet2 to range "A1:G400"sheet2.getPageLayout().setPrintArea("A1:G400");// Set print area for sheet2 to range "A1:G400"sheet2.getPageLayout().setPrintArea("A1:G400");// Set ExcelScript.PageOrientation.portrait orientation for sheet2sheet2.getPageLayout().setOrientation(ExcelScript.PageOrientation.portrait);// Set Letter paperSize for sheet2sheet2.getPageLayout().setPaperSize(ExcelScript.PaperType["Letter"]);// Set No Scaling scaling for sheet2sheet2.getPageLayout().setZoom({scale: 100});// Set horizontal alignment to ExcelScript.HorizontalAlignment.left for range A:A on sheet2sheet2.getRange("A:A").getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.left);// Indent set to 0 for range A:A on sheet2sheet2.getRange("A:A").getFormat().setIndentLevel(0);// Set horizontal alignment to ExcelScript.HorizontalAlignment.center for range 1:1 on sheet2sheet2.getRange("1:1").getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.center);sheet2.getRange("1:1").getFormat().setIndentLevel(0);// Indent set to 0 for range 1:1 on sheet2sheet2.getRange("1:1").getFormat().setIndentLevel(0);// Set font bold to true for range 1:1 on sheet2sheet2.getRange("1:1").getFormat().getFont().setBold(true);}
- Thanks. Looks like the recorded script places the hard-coded text "email address removed for privacy reasons" into column E, is that not what you need? Which formula did you intend to get in column E?
Each row has a different email address and correct, I want a portion of the email address to be removed and replaced with x's for privacy reasons, however when I run the script it fills every single row with the concealed email address from the first row. Does that make sense? JKPieterse
