Error when recording a macro (argument is invalid or missing or has an incorrect format)

[8932LDG]

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: 

  selectedSheet.getRange("J2").setFormulaLocal("=IF(F2=\"\",\"\",VLOOKUP(IF(VLOOKUP(F2,R2C1:R1400C9,4,FALSE)=0,VLOOKUP(F2,R2C1:R1400C9,6,FALSE),F2),R2C1:R1400C9,9,FALSE))");

When recording the macro I entered this formula on the cell J2: 

 =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))

The formula worked fine while I was recording it. Anybody has any idea what could I have done wrong?

[#2]

@8932LDG 

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.

Macro Error

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.

[#3]

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!

[#4]

@Manju1685 

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.

[#5]

HI,

Thanks for the reply but i stilll have error
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: Range setFormulaLocal: The argument is invalid or missing or has an incorrect format.

I use ; in my excel for all formulas

[#6]

Adjust the code to use semicolons (;) instead of commas (,) as argument separators in the formula, maybe this is the problem.

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

[#7]

Thanks a lot!

[#8]

How can i use office script to create worksheet in Excel with particular text followed by current date and time as its name