当前位置: 动力学知识库 > 问答 > 编程问答 >

excel - Run-time error 13 when highlighting entire row

问题描述:

Whenever I try to highlight over column D I receive "Run-time error '13'". When I click debug it highlights this piece from the code, If Target.Value = "Closed" Or Target.Value = "Closed" Then. I would greatly appreciate any advice on how to fix this issue.

Edit*

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim wsd As Worksheet

Dim wsc As Worksheet

Application.ScreenUpdating = False

Set wsd = Sheets("Pipeline")

Set wsc = Sheets("Closed")

If Not Intersect(Target, Range("D6:D65536")) Is Nothing Then

If Target.Value = "Closed" Or Target.Value = "Closed" Then

erow = Target.Row

MsgBox "Moved to Closed"

numberofrow = wsc.Range("A65536").End(xlUp).Row

For i = 1 To numberofrow

If wsd.Cells(erow, 1) = wsc.Cells(i, 1) Then

Exit Sub

End If

Next

wsd.Range("A" & erow & ":A" & erow).EntireRow.Copy wsc.Range("A" & numberofrow + 1)

With Target.Parent

Union(.Range(.Cells(Target.Row, "A"), .Cells(Target.Row, "V")), _

.Range(.Cells(Target.Row, "Y"), .Cells(Target.Row, .Columns.Count))).ClearContents

`enter code here`End With

wsc.Activate

ActiveSheet.Range("A2:D" & numberofrow + 1).Select

ActiveWorkbook.Worksheets("Closed").Sort.SortFields.Clear

ActiveWorkbook.Worksheets("Closed").Sort.SortFields.Add Key:=Range("C2:C" & numberofrow + 1), _

SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets("Closed").Sort

.SetRange Range("A2:D" & numberofrow + 1)

.Header = xlYes

.MatchCase = False

.Orientation = xlTopToBottom

.SortMethod = xlPinYin

.Apply

End With

wsd.Activate

End If

End If

End Sub

I had to piece a few things together in order to get this to work, which is why it's pretty ugly. I couldn't figure out where to put guard clause. This so far has worked for what I need it for (aside from the Run-time error), transferring accounts to a separate sheet based on the sales stage entered into column D. Again, I appreciate all the help.

网友答案:

Your handler assumes only 1 cell is selected. When you highlight an entire column, Target represents all selected cells, and then Target.Value can't be meaningfully evaluated, so it raises a run-time error.

You need to modify your handler to remove that assumption, as was suggested:

If Target.Cells.Count > 1 Then Exit Sub

Presumably that event handler needs more tweaks to verify the Target is what the macro thinks/assumes it is, but given the information you've provided that would be it.

分享给朋友:
您可能感兴趣的文章:
随机阅读: