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

dilshod_k

New Member
Joined
Feb 13, 2018
Messages
49
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.
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,811
Office Version
  1. 2010
Platform
  1. Windows
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.)
 
Last edited:

dilshod_k

New Member
Joined
Feb 13, 2018
Messages
49
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. :biggrin: Thanks for an attempt to help anyway.
 
Last edited:

dilshod_k

New Member
Joined
Feb 13, 2018
Messages
49
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. :biggrin:Thanks for advice anyway.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,083
Messages
5,526,753
Members
409,717
Latest member
Oscarsalone

This Week's Hot Topics

Top