首页 文章

找不到匹配错误的方法[重复]

提问于
浏览 次
-3

这个问题在这里已有答案：

我一直得到一个“运行时错误'91'：对象变量或没有设置块变量 . 我知道问题是find方法找不到匹配，但我似乎无法找到如何解决问题 . 我有尝试了If Not xxx Is Nothing ...但是我又得到了另一个错误 .

Sub SetUpFormulas50450()

Dim cnt1 As Long
Dim i As Long
Dim lnRow As Long
Dim lnCol As Long
Dim lnCol1 As Long
Dim lnCol4 As Long
Dim lnRow1 As Long
Dim tempA As Long
Dim tempB As Long
Dim a As Long
Dim LR As Long

Set Sh1 = ThisWorkbook.Worksheets("Pivot")
Sh1.Select

lnRow = 5
lnCol = 2

cnt1 = Sh1.Cells(Rows.Count, "A").End(xlUp).Row

'Sh1.Cells(1, 10) = cnt1

lnCol1 = Sheet1.Cells(lnRow, 1).EntireRow.Find(What:="Grand Total", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Column
'Sh1.Cells(1, 11) = lnCol1

Sh1.Cells(cnt1, (lnCol1 + 2)).Formula = "=SUM(" & Range(Cells(6, (lnCol1 + 2)), Cells((cnt1 - 1), (lnCol1 + 2))).Address(False, False) & ")"

'Calculate Frt Pct of Sales
For i = 6 To cnt1
    tempA = Sh1.Cells(i, lnCol1).Value
    tempB = Sh1.Cells(i, (lnCol1 + 2)).Value
    If Sh1.Cells(i, (lnCol1 + 2)).Value = 0 Then Sh1.Cells(i, (lnCol1 + 3)).Value = 0 Else Sh1.Cells(i, (lnCol1 + 3)).Value = tempA / tempB
Next i

'Calculate Frt Pd in Fut Mos
For a = 2 To (lnCol1 - 2)
    For i = 6 To (cnt1 - 2)
        lnCol4 = Sheet1.Cells(lnRow, 1).EntireRow.Find(What:=Sh1.Cells(i, 1).Value, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Column
        Sh1.Cells(i, (lnCol1 + 4)).Formula = "=SUM(" & Range(Cells(i, (lnCol4 + 1)), Cells(i, (lnCol1 - 1))).Address(False, False) & ")"
    Next i
Next a
End Sub

我遇到了问题：

lnCol4 = Sheet1.Cells(lnRow, 1).EntireRow.Find(What:=Sh1.Cells(i, 1).Value, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Column
vba excel-vba find match excel

1 回答

  • 1
    Dim f As Range '<-- declare a range to collect the result of 'Find()' method
For a = 2 To (lnCol1 - 2)
    For i = 6 To (cnt1 - 2)
        Set f = Sheet1.Cells(lnRow, 1).EntireRow.Find(What:=Sh1.Cells(i, 1).Value, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False) '<-- try setting f to the 'Find()' method result
        If Not f Is Nothing Then '<--| check for f to be a "real" range
            lnCol4 = f.Column
            Sh1.Cells(i, (lnCol1 + 4)).Formula = "=SUM(" & Range(Cells(i, (lnCol4 + 1)), Cells(i, (lnCol1 - 1))).Address(False, False) & ")"
        End If
    Next i
Next a
    回复于

相关问题