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 worksheet
let sheet2 = workbook.addWorksheet();
// Set range A1:G2 on sheet2
sheet2.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 sheet2
sheet2.getRange("A:A").setNumberFormatLocal("[$-en-US]mmmm d, yyyy;@");
// Auto fit the columns of all cells on sheet2
sheet2.getRange().getFormat().autofitColumns();
// Auto fill range
sheet2.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 sheet2
sheet2.getPageLayout().setOrientation(ExcelScript.PageOrientation.portrait);
// Set Letter paperSize for sheet2
sheet2.getPageLayout().setPaperSize(ExcelScript.PaperType["Letter"]);
// Set No Scaling scaling for sheet2
sheet2.getPageLayout().setZoom({scale: 100});
// Set horizontal alignment to ExcelScript.HorizontalAlignment.left for range A:A on sheet2
sheet2.getRange("A:A").getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.left);
// Indent set to 0 for range A:A on sheet2
sheet2.getRange("A:A").getFormat().setIndentLevel(0);
// Set horizontal alignment to ExcelScript.HorizontalAlignment.center for range 1:1 on sheet2
sheet2.getRange("1:1").getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.center);
sheet2.getRange("1:1").getFormat().setIndentLevel(0);
// Indent set to 0 for range 1:1 on sheet2
sheet2.getRange("1:1").getFormat().setIndentLevel(0);
// Set font bold to true for range 1:1 on sheet2
sheet2.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
acklenk What you would need to do is add a new column which contains a formula that changes the email address to how you want it to look. Suppose the current email address is in cell E2, that formula could be something like this:
="xxx.xxxxxx@"&TEXTAFTER(E2,"@")Then once the formula is as you want it, record a script where you edit that formula cell so you can see its syntax and add this to your current script.