gpt4 book ai didi

vba - "Subscript out of range"错误,需要复制和粘贴标题行来创建枢轴

转载 作者:行者123 更新时间:2023-12-04 21:54:40 24 4
gpt4 key购买 nike

我有以下代码,它将从 Sheet1 复制并粘贴符合 E 列中的关键字条件(里程碑)的行,并将行复制到 Sheet2。

然后在 Sheet2 上提取日期并输入新列,但我收到错误

Subscript out of range (Error 9)



当这个 Action 完成时。

我看不出是什么原因造成的?

我还需要先将 Sheet1 的第 10 行复制并粘贴到 Sheet2 的第 1 行,以用作数据透视表的类别?
Sub mileStoneDateChanger()

Dim r As Long, pasteRowIndex As Long, v() As Long, i As Long

Dim lastRow As Long


Dim lCol As Long

lastRow = Cells.Find(What:="*", _
After:=Range("A1"), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row

MsgBox "Last Row: " & lastRow



pasteRowIndex = 1

With Sheets("Sheet1")
For r = 1 To lastRow
If .Cells(r, "E").Value Like "Milestone*" Then
If UBound(Split(.Cells(r, "E"), ",")) > 0 Then
i = i + 1
ReDim v(1 To i)
v(i) = pasteRowIndex
End If
Sheets("Sheet1").Rows(r).Copy Sheets("Sheet2").Rows(pasteRowIndex)
pasteRowIndex = pasteRowIndex + 1
End If
Next r
End With



With Sheets("Sheet2")

newLastRow = Cells.Find(What:="*", _
After:=Range("B1"), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row

If IsArray(v) Then
.Columns(6).Insert shift:=xlToRight
For i = 1 To newLastRow
.Cells(i, "F").Value = Split(.Cells(i, "E"), ",")(1)
Next i
End If


End With

End Sub

最佳答案

您收到超出范围的错误,因为您假设 Cells(i,"E") 中有逗号这里:

For i = 1 To newLastRow
.Cells(i, "F").Value = Split(.Cells(i, "E"), ",")(1)
Next i

使用上面的代码,您获取数组的第二个值,来自拆分后的 Cells(i,"E") , 用逗号。如果单元格中的值为 123,45 , 你会拿 45 .最有可能的情况是,您没有任何逗号,因此没有第二个值。所以,你必须做一个检查。例如。像这样的东西:
If InStr(1, .Cells(i, "F"), ",") Then
.Cells(i, "F").Value = Split(.Cells(i, "E"), ",")(1)
End If

关于vba - "Subscript out of range"错误,需要复制和粘贴标题行来创建枢轴,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/47392774/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com