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
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);
}
}