May be a Chinese Windows problem??

G'day from Oz. The following macro works perfectly when inserting a
new line(s) into an established spreadsheet. However, when I send the
spreadsheet to our Hong Kong or Taiwan branch, the macro fails "Error
Code 13" with the asterisked line highlighted in yellow. Is this a
problem with Chinese Windows incompatibility?
TIA for your help, Dave


Colon = InStr(ActiveWindow.RangeSelection.Address, ":")
If Colon = 0 Then
MsgBox "Please select Rows to be inserted"
Exit Sub
End If
FirstRow = Left(ActiveWindow.RangeSelection.Address, Colon - 1)
LastRow = Mid(ActiveWindow.RangeSelection.Address, Colon + 1)
MyCheck1 = IsNumeric(Mid(FirstRow, 2, 1)) 'Allows for $ lead
character
If MyCheck1 = False Then
MsgBox "Please select Rows to be inserted"
Exit Sub
End If

Rows(FirstRow & ":" & LastRow).Select '***********
Selection.Copy
Rows(LastRow + 1 & ":" & LastRow + 1).Select
Selection.Insert Shift:=xlDown

Range("A" & FirstRow & ":D" & LastRow).ClearContents

Dave,
I can confirm this fails on HK Chinese Excel 2K but works on English Excel
2K, although I get a Type Mismatch error on the line:
Rows(LastRow + 1 & ":" & LastRow + 1).Select
Not sure why there should be a difference, but why not use the properties of
the selected range, instead of trying to split the address. e.g.

Private Sub CommandButton1_Click()
Dim SelRange As Range

On Error GoTo Handler
Set SelRange = Selection

FirstRow = SelRange.Row
LastRow = FirstRow + SelRange.Rows.Count - 1

Selection.Copy
Cells(LastRow + 1, 1).Select
Selection.Insert Shift:=xlDown

Range("A" & FirstRow & ":D" & LastRow).ClearContents

Exit Sub
Handler:
Select Case Err.Number
Case 438 'No range selected
MsgBox "Select a range"
Case Else
End Select

End Sub

Probably need some error checking, but you get the idea.

NickHK

"AussieDave" <[email protected]> wrote in message
news:[email protected]...
> G'day from Oz. The following macro works perfectly when inserting a
> new line(s) into an established spreadsheet. However, when I send the
> spreadsheet to our Hong Kong or Taiwan branch, the macro fails "Error
> Code 13" with the asterisked line highlighted in yellow. Is this a
> problem with Chinese Windows incompatibility?
> TIA for your help, Dave
>
>
> Colon = InStr(ActiveWindow.RangeSelection.Address, ":")
> If Colon = 0 Then
> MsgBox "Please select Rows to be inserted"
> Exit Sub
> End If
> FirstRow = Left(ActiveWindow.RangeSelection.Address, Colon - 1)
> LastRow = Mid(ActiveWindow.RangeSelection.Address, Colon + 1)
> MyCheck1 = IsNumeric(Mid(FirstRow, 2, 1)) 'Allows for $ lead
> character
> If MyCheck1 = False Then
> MsgBox "Please select Rows to be inserted"
> Exit Sub
> End If
>
> Rows(FirstRow & ":" & LastRow).Select '***********
> Selection.Copy
> Rows(LastRow + 1 & ":" & LastRow + 1).Select
> Selection.Insert Shift:=xlDown
>
> Range("A" & FirstRow & ":D" & LastRow).ClearContents
>

NickHK wrote:
> Dave,
> I can confirm this fails on HK Chinese Excel 2K but works on English Excel
> 2K, although I get a Type Mismatch error on the line:
> Rows(LastRow + 1 & ":" & LastRow + 1).Select
> Not sure why there should be a difference, but why not use the properties of
> the selected range, instead of trying to split the address. e.g.
>
> Private Sub CommandButton1_Click()
> Dim SelRange As Range
>
> On Error GoTo Handler
> Set SelRange = Selection
>
> FirstRow = SelRange.Row
> LastRow = FirstRow + SelRange.Rows.Count - 1
>
> Selection.Copy
> Cells(LastRow + 1, 1).Select
> Selection.Insert Shift:=xlDown
>
> Range("A" & FirstRow & ":D" & LastRow).ClearContents
>
> Exit Sub
> Handler:
> Select Case Err.Number
> Case 438 'No range selected
> MsgBox "Select a range"
> Case Else
> End Select
>
> End Sub
>
> Probably need some error checking, but you get the idea.
>
> NickHK
>
> "AussieDave" <[email protected]> wrote in message
> news:[email protected]...
> > G'day from Oz. The following macro works perfectly when inserting a
> > new line(s) into an established spreadsheet. However, when I send the
> > spreadsheet to our Hong Kong or Taiwan branch, the macro fails "Error
> > Code 13" with the asterisked line highlighted in yellow. Is this a
> > problem with Chinese Windows incompatibility?
> > TIA for your help, Dave
> >
> >
> > Colon = InStr(ActiveWindow.RangeSelection.Address, ":")
> > If Colon = 0 Then
> > MsgBox "Please select Rows to be inserted"
> > Exit Sub
> > End If
> > FirstRow = Left(ActiveWindow.RangeSelection.Address, Colon - 1)
> > LastRow = Mid(ActiveWindow.RangeSelection.Address, Colon + 1)
> > MyCheck1 = IsNumeric(Mid(FirstRow, 2, 1)) 'Allows for $ lead
> > character
> > If MyCheck1 = False Then
> > MsgBox "Please select Rows to be inserted"
> > Exit Sub
> > End If
> >
> > Rows(FirstRow & ":" & LastRow).Select '***********
> > Selection.Copy
> > Rows(LastRow + 1 & ":" & LastRow + 1).Select
> > Selection.Insert Shift:=xlDown
> >
> > Range("A" & FirstRow & ":D" & LastRow).ClearContents
> >
Thanks Nick, a couple of little tweaks and I've sent it to my HK girls
who've tested it and they've asked me to say thanks a lot - it works
perfectly. Cheers, Dave