Error 1004 'Range' when using French Regional options but not Engl

[JR]

I have inherited someone else's macro code, which is never easy. It is stored
in a module, not as code on the worksheet.

The macro copies data from several other worksheets to a summary worksheet
and then formats it. The following snippet causes an error, but only when the
regional settings in Control Panel are set to a language other than English,
and only when it is run directly from the control on the worksheet, not when
I step through it. I can't just insist it runs on an English system as it is
used by people in other countries.

This is the error:
"Run-time error '1004'
Method 'Range' of object '_worksheet' failed"

and this is the offending piece of code (the first line is where it stops,
after it has done the first instance):
For Each myCell In
Sh.Range("E3,K3,K5,M3,E5,I8,E4,K4,D8,M8,N26,N27,N28,N31,N32,N33")
'<--Change the range
ColNum = ColNum + 1
Newsh.Cells(RwNum, ColNum).Value = myCell.Value
'Newsh.Cells(RwNum, ColNum).Formula = _
"='" & Sh.Name & "'!" & myCell.Address(False, False)
Newsh.Cells(RwNum, ColNum).Borders.LineStyle = xlSolid
Newsh.Cells(RwNum, ColNum).Font.Size = 8
Newsh.Cells(RwNum, ColNum).VerticalAlignment = xlTop
Next myCell

I have had a play, and the error doesn't happen if there is only one cell
mentioned in the first line. But if there is more than one cell in that first
line, it falls over.

Any suggestions?

thanks.

 

[Patrick Molloy]

both these variables should be DIm'd and set
sh
newsh

here is my test code

Option Explicit
Sub xxx()

Dim sh As Worksheet
Dim Newsh As Worksheet
Dim myCell As Range
Dim RwNum As Long
Dim ColNum As Long

Set sh = Worksheets("sheet1")
Set Newsh = Worksheets.Add
RwNum = 1

For Each myCell In
ActiveSheet.Range("E3,K3,K5,M3,E5,I8,E4,K4,D8,M8,N26,N27,N28,N31,N32,N33").Cells
'<--Change the range
ColNum = ColNum + 1
Newsh.Cells(RwNum, ColNum).Value = myCell.Value
'Newsh.Cells(RwNum, ColNum).Formula = _
"='" & Sh.Name & "'!" & myCell.Address(False, False)
Newsh.Cells(RwNum, ColNum).Borders.LineStyle = xlSolid
Newsh.Cells(RwNum, ColNum).Font.Size = 8
Newsh.Cells(RwNum, ColNum).VerticalAlignment = xlTop
Next myCell
End Sub

 

[JR]

Hi Patrick

Thanks, but unfortunately they were already DIMed in the rest of the code,
so that didn't fix it.

I notice I forgot to say what version of Excel, it's 2003.

In case the problem is somewhere else in this macro, here is the full code.
It's not massive, and it works when the locale is set to English, it's just
when you change the locale that it doesn't work!

Sub Summary_All_Worksheets()
Dim Sh As Worksheet
Dim Newsh As Worksheet
Dim myCell As Range
Dim ColNum As Long
Dim RwNum As Long
Dim Basebook As Workbook
Dim issueCell As Range
Dim issues As String

'' turn off screen updating, to speed it up and avoid flashing.
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

'' Identify which worksheet values are copied to, and turn off protection.
Set Basebook = ThisWorkbook
Set Newsh = Basebook.Worksheets("PSR Summary")
Newsh.Protect Scenarios:=False, Contents:=False, UserInterfaceOnly:=False
Newsh.Rows("7:" & Newsh.Rows.Count).Clear

'' the format of the cell the date is copied into is already dd mmmm yyyy so
no need to specify the date format.
' Insert the creation date
Newsh.Cells(3, 2).Value = Date
Newsh.Cells(3, 2).Font.Size = 9

'' This is because all of the sheets from which the values are copied just
have headings on rows 1 - 6
'The links to the first sheet will start in row 6
RwNum = 6

'' This is to make the macro ignore three other worksheets in the workbook
that should not be copied.
For Each Sh In Basebook.Worksheets
If IsError(Application.Match(Sh.Name, _
Array(Newsh.Name, "Instructions",
"PSR-Example", "PSR MASTER"), 0)) Then

If Sh.Name <> Newsh.Name And Sh.Visible Then
ColNum = 1
RwNum = RwNum + 1

'Copy the sheet name in the A column
Newsh.Cells(RwNum, 1).Value = Sh.Name
Newsh.Cells(RwNum, 1).Borders.LineStyle = xlSolid
Newsh.Hyperlinks.Add Anchor:=Newsh.Cells(RwNum, 1), Address:="",
SubAddress:="'" + Sh.Name + "'!A1"
Newsh.Cells(RwNum, 1).Font.Size = 8
Newsh.Cells(RwNum, 1).VerticalAlignment = xlTop


'' Merges the contents of several cells in the source worksheet and copies
them into a sincle cell in the Summary worksheet and adds a border and font
formatting.
'Create the contents of the issues field
issues = ""
For Each issueCell In Sh.Range("D2831")
If issueCell > "" Then
If issueCell <> "<summary>" Then
issues = issues + issueCell.Value + " * "
End If
End If
Next issueCell
Newsh.Cells(RwNum, 18).Value = issues
Newsh.Cells(RwNum, 18).Font.Size = 8
Newsh.Cells(RwNum, 18).Borders.LineStyle = xlSolid
Newsh.Cells(RwNum, 18).WrapText = True
Newsh.Cells(RwNum, 18).VerticalAlignment = xlTop

''THIS IS WHERE IT BREAKS IN FRENCH LOCALE, AS SOON AS THERE IS MORE THAN
ONE CELL REFERENCED INSIDE THE BRACKETS.

'Main Content
For Each myCell In
ActiveSheet.Range("E3,K3,K5,M3,E5,I8,E4,K4,D8,M8,N26,N27,N28,N31,N32,N33").Cells
'<--Change the range
ColNum = ColNum + 1
Newsh.Cells(RwNum, ColNum).Value = myCell.Value
Newsh.Cells(RwNum, ColNum).Borders.LineStyle = xlSolid
Newsh.Cells(RwNum, ColNum).Font.Size = 8
Newsh.Cells(RwNum, ColNum).VerticalAlignment = xlTop
Next myCell


End If

End If
Next Sh

With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With

'Set Overall RAG status cell background colour

Dim ragArea As Range
Dim ragCell As Range

Set ragArea = Newsh.Range("G:G")
For Each ragCell In ragArea
With ragCell
If Not IsError(.Value) Then
Select Case .Value
Case "R", "r"
.Interior.ColorIndex = 3
.HorizontalAlignment = xlCenter
.Font.ColorIndex = 2
.Font.Bold = True
Case "A", "a"
.Interior.ColorIndex = 45
.HorizontalAlignment = xlCenter
.Font.ColorIndex = 2
.Font.Bold = True
Case "G", "g"
.Interior.ColorIndex = 43
.HorizontalAlignment = xlCenter
.Font.ColorIndex = 2
.Font.Bold = True
Case "B", "b"
.Interior.ColorIndex = 32
.HorizontalAlignment = xlCenter
.Font.ColorIndex = 2
.Font.Bold = True
End Select
End If
End With
Next

'Highlights Negative Project Cost Variances by red cell background colour
and positive in green

Dim pcvarArea As Range
Dim pcvarCell As Range

Set pcvarArea = Newsh.Range("N7:N300")
For Each pcvarCell In pcvarArea
With pcvarCell
If Not IsError(.Value) Then
Select Case .Value
Case Is < 0
.Interior.ColorIndex = 3
.Font.ColorIndex = 2
.Font.Bold = True
.NumberFormat = "#,##0;(#,##0)"
Case Is > 0
.Interior.ColorIndex = 43
.Font.ColorIndex = 2
.Font.Bold = True
.NumberFormat = "#,##0;(#,##0)"
End Select
End If
End With
Next

'Highlights Negative Project Budget Variances by red cell background
colour and positive in green

Dim pbvarArea As Range
Dim pbvarCell As Range

Set pbvarArea = Newsh.Range("Q7:Q300")
For Each pbvarCell In pbvarArea
With pbvarCell
If Not IsError(.Value) Then
Select Case .Value
Case Is < 0
.Interior.ColorIndex = 3
.Font.ColorIndex = 2
.Font.Bold = True
.NumberFormat = "#,##0;(#,##0)"
Case Is > 0
.Interior.ColorIndex = 43
.Font.ColorIndex = 2
.Font.Bold = True
.NumberFormat = "#,##0;(#,##0)"
End Select
End If
End With
Next

'Newsh.UsedRange.Rows.AutoFit
Newsh.UsedRange.Columns.AutoFit
Columns("R:R").ColumnWidth = 24

Newsh.Protect Scenarios:=True, Contents:=True, UserInterfaceOnly:=True


End Sub

 

[Patrick Molloy]

I was hoping that somebody with a French version might sep in and answer.

What value do you see for the constants, eg xlTop is -4160
mind you, I think this is probably the wrong route

 

[JR]

The error happens if the LOCALE is set to French, even when using an English
installation of Excel, so I don't think that's where the issue lies.

As for xlTop, it isn't a constant it's a formatting setting, so I don't see
what you mean.

 

[JR]

Jim, you are a star! Thank you so much, I would never have worked that one
out. It worked a treat, on my system at least. Now to send the modified
worksheet over to my French colleagues to see if it still works.

JR