May be a Chinese Windows problem??

[AussieDave]

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 & "" & LastRow).ClearContents

 

[NickHK]

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 & "" & 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]

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 & "" & 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

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