Unable to set the NumberFormat property of the Range class; Run-time error “1004”

[dilshod_k]

I have an excel workbook which downloads historical stocks price data.
The VBA code has been working without any problems for at least couple of years.
Today I get error message: Run-time error “1004” - Unable to set the NumberFormat property of the Range class

part of VBA code as it follows below:

Set myRange = ThisWorkbook.Worksheets("Control").Range("ControlExtractDataNumberHeading")
Set myRange = Range(myRange, myRange.Offset(0, intYPEAdjustedClosePrice - 3))
Set myRange = Range(myRange, myRange.End(xlDown))
myRange.NumberFormat = Application.International(xlCurrencyCode) & "#,##0.00" - ERROR MESSAGE AT THIS STEP!!!
myRange.Value = myRange.Value2

I'm using Microsoft Office Professional Plus 2016
Windows 10

Thanks in advance for suggestions.

 

[jkpieterse]

Any merged cells in the range?

 

[joeu2004]

I get no error when I execute:

Range("a1:a10").NumberFormat = Application.International(xlCurrencyCode) & "#,##0.00"

So my guess is: (a) you unconsciously corrected the problem when you posted the code; or (b) VBA is misleading you or you are otherwise misinterpreting where the error occurs; or (c) myRange is not what you think it is, perhaps because (if) you executed On Error Resume Next beforehand. (But arguably, that also would have "hidden" the error that you got, unless you also executed On Error GoTo 0 beforehand.)

I suggest that you enter the following statement after each Set statement:

Debug.Print myRange.Address(True,True,external:=True)

After execution, press ctrl+G to see the Debug.Print output in the Immediate Window.

If the problem persists, also execute Debug.Print Application.International(xlCurrencyCode), and include all Debug.Print output in a follow-up posting.

PS.... I wonder if another possible source of the VBA error is that you have "too many" formats in the Excel file. What happens if you try to do the same thing manually in Excel? (In the same Excel file, of course.)

 

[dilshod_k]

Any merged cells in the range?

No merged cells.
I think I found the problem. Recently I changed Windows Region/Format settings to English (Europe), apparently that was the cause of the problem. As soon as I changed it back to English (United States) error message disappeared. Thanks for an attempt to help anyway.

 

[dilshod_k]

I get no error when I execute:

Range("a1:a10").NumberFormat = Application.International(xlCurrencyCode) & "#,##0.00"

So my guess is: (a) you unconsciously corrected the problem when you posted the code; or (b) VBA is misleading you or you are otherwise misinterpreting where the error occurs; or (c) myRange is not what you think it is, perhaps because (if) you executed On Error Resume Next beforehand. (But arguably, that also would have "hidden" the error that you got, unless you also executed On Error GoTo 0 beforehand.)

I suggest that you enter the following statement after each Set statement:

Debug.Print myRange.Address(True,True,external:=True)

After execution, press ctrl+G to see the Debug.Print output in the Immediate Window.

If the problem persists, also execute Debug.Print Application.International(xlCurrencyCode), and include all Debug.Print output in a follow-up posting.

PS.... I wonder if another possible source of the VBA error is that you have "too many" formats in the Excel file. What happens if you try to do the same thing manually in Excel? (In the same Excel file, of course.)

I think I found the problem. Recently I changed Windows Region/Format settings to English (Europe), apparently that was the cause of the problem. As soon as I changed it back to English (United States) error message disappeared. Thanks for advice anyway.