J
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.
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.