exceloffice-scripts

365 Excel: Using Code Editor to Stop a Macro on Empty Cell


Most Likely stupid question here but...

I have a simple recorded macro in Excel, copies a line from sheet1 to sheet2 after creating a new line in sheet2, then clears the cells used in sheet1, simple works like a charm.

Now.. a single cells is a requirement, so needs to be filled in, and want to add a small bit of code to stop the macro if the cell is empty. which I've found about 20 different ways of doing this in VBA in various versions over the years. however 365 Excel has macro editing in "code Editor" which apparently doesn't recognise many of the lines that VBA uses. all erroring and I'm at a loss.

I'll admit it's more likely i'm just putting it in the wrong place or missing a comma or something, but not knowing VBA and only copying the code is only ever failing. any help you can offer would be great.

This is the unedited code that works

function main(workbook: ExcelScript.Workbook) {
    let _2024 = workbook.getWorksheet("2024");
    // Insert at range 3:3 on _2024, move existing cells down
    _2024.getRange("3:3").insert(ExcelScript.InsertShiftDirection.down);
    let selectedSheet = workbook.getActiveWorksheet();
    // Paste to range A3 on _2024 from range B19:N19 on selectedSheet
    _2024.getRange("A3").copyFrom(selectedSheet.getRange("B19:N19"), ExcelScript.RangeCopyType.values, false, false);
    // Set range N1 on _2024
    _2024.getRange("N1").setFormulaLocal("=C3+1");
    // Paste to range D19 on selectedSheet from range N1 on _2024
    selectedSheet.getRange("D19").copyFrom(_2024.getRange("N1"), ExcelScript.RangeCopyType.values, false, false);
    // Set range D13 on selectedSheet
    selectedSheet.getRange("D13").setValue("FALSE");
    // Set range D11 on selectedSheet
    selectedSheet.getRange("D11").setValue("FALSE");
    // Set range D7 on selectedSheet
    selectedSheet.getRange("D7").setValue("FALSE");
    // Set range G7 on selectedSheet
    selectedSheet.getRange("G7").setValue("FALSE");
    // Set range D9 on selectedSheet
    selectedSheet.getRange("D9").setFormulaLocal("=C2");
    // Clear ExcelScript.ClearApplyTo.contents from range G11 on selectedSheet
    selectedSheet.getRange("G11").clear(ExcelScript.ClearApplyTo.contents);
    // Clear ExcelScript.ClearApplyTo.contents from range G9 on selectedSheet
    selectedSheet.getRange("G9").clear(ExcelScript.ClearApplyTo.contents);
    // Clear ExcelScript.ClearApplyTo.contents from range D5:G5 on selectedSheet
    selectedSheet.getRange("D5:G5").clear(ExcelScript.ClearApplyTo.contents);
    // Clear ExcelScript.ClearApplyTo.contents from range G3 on selectedSheet
    selectedSheet.getRange("G3").clear(ExcelScript.ClearApplyTo.contents);
    // Clear ExcelScript.ClearApplyTo.contents from range D3 on selectedSheet
    selectedSheet.getRange("D3").clear(ExcelScript.ClearApplyTo.contents);
}

I've tried a variety of code from various sites including here, but all seem to show issues even with simple cmdlet names for example

Sub Submission()

    If Range("N10") = "1" Then
      MsgBox "Please complete required fields"
      Range("N10").Select
    Else
    End If
End Sub

Sub Submission shows an error Range shows an error Then shows an error End Sub shows an error

Thanks in Advance to anyone generous enough to help with this likely ID10T error


Solution

  • Add an if-else clause to validate cell N10

    function main(workbook: ExcelScript.Workbook) {
      let _2024 = workbook.getWorksheet("2024");
      let selectedSheet = workbook.getActiveWorksheet();
      let n10 = selectedSheet.getRange("N10").getText();
      if(n10 === "1"){
        console.log("Please complete required fields")
        selectedSheet.getRange("N10").select();
      } else {
        // Insert at range 3:3 on _2024, move existing cells down
        _2024.getRange("3:3").insert(ExcelScript.InsertShiftDirection.down);
        // Paste to range A3 on _2024 from range B19:N19 on selectedSheet
        _2024.getRange("A3").copyFrom(selectedSheet.getRange("B19:N19"), ExcelScript.RangeCopyType.values, false, false);
        // Set range N1 on _2024
        _2024.getRange("N1").setFormulaLocal("=C3+1");
        // Paste to range D19 on selectedSheet from range N1 on _2024
        selectedSheet.getRange("D19").copyFrom(_2024.getRange("N1"), ExcelScript.RangeCopyType.values, false, false);
        // Set range D13 on selectedSheet
        selectedSheet.getRange("D13").setValue("FALSE");
        // Set range D11 on selectedSheet
        selectedSheet.getRange("D11").setValue("FALSE");
        // Set range D7 on selectedSheet
        selectedSheet.getRange("D7").setValue("FALSE");
        // Set range G7 on selectedSheet
        selectedSheet.getRange("G7").setValue("FALSE");
        // Set range D9 on selectedSheet
        selectedSheet.getRange("D9").setFormulaLocal("=C2");
        // Clear ExcelScript.ClearApplyTo.contents from range G11 on selectedSheet
        selectedSheet.getRange("G11").clear(ExcelScript.ClearApplyTo.contents);
        // Clear ExcelScript.ClearApplyTo.contents from range G9 on selectedSheet
        selectedSheet.getRange("G9").clear(ExcelScript.ClearApplyTo.contents);
        // Clear ExcelScript.ClearApplyTo.contents from range D5:G5 on selectedSheet
        selectedSheet.getRange("D5:G5").clear(ExcelScript.ClearApplyTo.contents);
        // Clear ExcelScript.ClearApplyTo.contents from range G3 on selectedSheet
        selectedSheet.getRange("G3").clear(ExcelScript.ClearApplyTo.contents);
        // Clear ExcelScript.ClearApplyTo.contents from range D3 on selectedSheet
        selectedSheet.getRange("D3").clear(ExcelScript.ClearApplyTo.contents);
    
      }
    }