Forum Discussion
Error when recording a macro (argument is invalid or missing or has an incorrect format)
I'm trying to automate a set of operations in Excel and recorded a macro.
The macro gives the following error:
Line 56: Range setFormulaLocal: The argument is invalid or missing or has an incorrect format.
This is what's on the script on line 56:
The issue you are facing is likely related to the way Excel handles formulas when recording macros. Excel records formulas using the R1C1 notation instead of the traditional A1 notation. In the recorded macro code, you are using the R1C1 notation in the formula.
To resolve the error, you can modify the code to use the A1 notation instead.
Here is the modified code:
selectedSheet.Range("J2").Formula = "=IF(F2="""","""",VLOOKUP(IF(VLOOKUP(F2,$A$2:$I$1400,4,FALSE)=0,VLOOKUP(F2,$A$2:$I$1400,6,FALSE),F2),$A$2:$I$1400,9,FALSE))"In the modified code, I replaced .setFormulaLocal with .Formula and used the A1 notation in the formula. This should resolve the "argument is invalid or missing or has an incorrect format" error.
Please note that if you are running the macro on a non-English version of Excel, you may need to adjust the formula to match the localized function names and syntax in your language.
In the end, however, without more information about your specific situation, it's difficult to say for sure that this will solve the problem. Try changing the code as suggested and see if that fixes the problem.
- function main(workbook: ExcelScript.Workbook) {
let selectedSheet = workbook.getActiveWorksheet();
// Set range B3 on selectedSheet
selectedSheet.getRange("B3").setFormulaLocal("=IF(SAPAnalyticsReportÜbersichtKP!A2<>\"\",SAPAnalyticsReportÜbersichtKP!B2)");
}
Line #4: ';' expected.
selectedSheet.getRange("B3").Formula = "=IF(SAPAnalyticsReportÜbersichtKP!A2<>"";SAPAnalyticsReportÜbersichtKP!B2)))"
Can anyone help me with this error!The error message you are encountering indicates that there is a syntax issue with the formula you're trying to set in cell B3. It seems like you are using JavaScript syntax for string concatenation, but the Excel Script language uses different syntax.
Here is the corrected version of your code:
function main(workbook: ExcelScript.Workbook) { let selectedSheet = workbook.getActiveWorksheet(); // Set formula in range B3 on selectedSheet selectedSheet.getRange("B3").setFormulaLocal('=IF(SAPAnalyticsReportÜbersichtKP!A2<>"", SAPAnalyticsReportÜbersichtKP!B2, "")'); }In Excel Script, you can use double quotes inside the formula string without escaping them. Also, note that the separator for function arguments in Excel formulas is a comma (,), not a semicolon (;). Additionally, I replaced the semicolon (;) in the comparison operator with a comma (,), as it's the correct syntax in Excel formulas. Finally, I added an empty string "" as the third argument in the IF function to specify the value if the condition is false. The text and the steps was created with the help of AI. My knowledge of this topic is limited, but since no one has answered it, I entered your question in various AI. If this not works provide more informations. Welcome to your Excel discussion space!
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
