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" wrote in message
ups.com...
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" wrote in message
ups.com...
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