excel - VBA Excel .Find 函数查找变量

Question

我是VBA的初学者。我正在编写一个代码，它将生成特定生产周期的 encast 项目报告。我正在努力使用带有变量的查找函数

我试图将我的变量声明为各种类型的数据。我试过字符串、变量、范围。如果我在括号中手动输入值，整个代码将起作用，但是如果我正在寻找变量，它将找不到任何东西

编辑：看起来我遇到了不匹配错误 13。在 Tim 的评论之后很明显，如果我关闭“On Error Resume Next statement”，就会发生这种情况。

Sub wypelnijtabelkedatapivot()


Dim rng1 As Range, rng2 As Range
Dim cell1 As Range, cell2 As Range
Dim lookitem As String
Dim lookproduct As String
Dim sheetlookitem As String

Dim wb As Workbook
Dim ws As Worksheet

Dim lnRow, lncolumn As Long

Dim kollookitem As Range   'should that be integer?
Dim rowlookitem As Range   'should that be integer?


Application.ScreenUpdating = False
Application.Calculation = xlManual

Columns(384).ClearContents

Set wb = ActiveWorkbook
Set ws = Sheets("datapivot")

With ws
    last = .Range("NU:NU").Find("Grand Total").Row
    Set rng2 = .Range(ws.Cells(10, 384), ws.Cells(last, 384))
    Set rng1 = .Range(ws.Cells(10, 1), ws.Cells(last, 383))
End With

lnRow = 31
lncolumn = 2



For Each cell2 In rng2

If Not Err <> 0 Then
cell2.Value = Left(Replace(Replace(Replace(cell2.Offset(0, 1), "PRE ", ""), "-", ""), " ", ""), 9)
Else
cell.Value = ""
End If
On Error GoTo 0
Next cell2

For Each cell1 In rng1
On Error Resume Next

lookitem = ws.Cells(9, cell1.Column)
lookproduct = ws.Cells(cell1.Row, 385)
sheetlookitem = ws.Cells(cell1.Row, 384).Value


'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'PROBLEM IS HERE'
'if I replace lookproduct variable with an actual value in bracket
'e.g "PRE BYL-05-0375-IW-0001", then the row number is returned.
'if I use variable, nothing is returned.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''


'''''''IT WILL FIND A VALUE as in here'''''''''''rowlookitem = wb.Sheets(sheetlookitem).Columns("B:B").Find(What:="PRE 05-BYL-0375-BS-0300", After:=ActiveCell, LookIn:=xlValues, _
''''''''''LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
''''''''''MatchCase:=False, SearchFormat:=False).Row

rowlookitem = wb.Sheets(sheetlookitem).Columns("B:B").Find(What:=lookproduct, After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Row

kollookitem = wb.Sheets(sheetlookitem).Rows("31:31").Find(What:=lookproduct, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Column



If Not Err <> 0 Then
cell1.Value = ActiveWorkbook.Worksheets(sheetlookitem).Cells(rowlookitem, kollookitem).Value




Else
cell.Value = ""
End If
On Error GoTo 0


Next cell1

Application.Calculation = xlAutomatic

End Sub

编辑：

Set rowlookitem = wb.Sheets(sheetlookitem).Columns("B:B").Find(What:=lookproduct, After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

If Not rowlookitem Is Empty Then
   rowlookitem.Value = rowlookitem.Row
Else
    MsgBox "nothing found"
End If

编辑2：

在玩了几个小时后，我终于设法让一切正常工作。我实际上是使用“noobish”方法制作的，但它有效！

如果有人感兴趣，这里有完整的代码。不匹配是问题所在。

Sub wypelnijtabelkedatapivot()


Dim rng1 As Range, rng2 As Range
Dim cell1 As Range, cell2 As Range
Dim lookitem As String
Dim lookproduct As String
Dim sheetlookitem As String

Dim wb As Workbook
Dim ws As Worksheet

Dim lnRow, lncolumn As Long

Dim kollookitem As Long   'should that be integer?
Dim rowlookitem As Long   'should that be integer?

Dim rngFound As Range, rngFound2 As Range: Set rngFound = Nothing

Application.ScreenUpdating = False
Application.Calculation = xlManual

Columns(384).ClearContents

Set wb = ActiveWorkbook
Set ws = Sheets("datapivot")

With ws
    last = .Range("NU:NU").Find("Grand Total").Row - 1
    Set rng2 = .Range(ws.Cells(10, 384), ws.Cells(last, 384))
    Set rng1 = .Range(ws.Cells(10, 1), ws.Cells(last, 383))
End With

lnRow = 31
lncolumn = 2



For Each cell2 In rng2

If Not Err <> 0 Then
cell2.Value = Left(Replace(Replace(Replace(cell2.Offset(0, 1), "PRE ", ""), "-", ""), " ", ""), 9)
Else
cell.Value = ""
End If
On Error GoTo 0
Next cell2

For Each cell1 In rng1
On Error Resume Next

lookitem = ws.Cells(9, cell1.Column)
lookproduct = Replace(ws.Cells(cell1.Row, 385), " ", "", 1, 1)
sheetlookitem = ws.Cells(cell1.Row, 384).Value
rowlookitem = Sheets(sheetlookitem).Range("B:B").Find(lookproduct).Row
kollookitem = Sheets(sheetlookitem).Range("31:31").Find(lookitem).Column

If Not Err <> 0 Then

cell1.Value = wb.Sheets(sheetlookitem).Cells(rowlookitem, kollookitem).Value
Else
cell1.Value = ""
End If
On Error GoTo 0

Next cell1

Application.Calculation = xlAutomatic

End Sub

标签： excelvba

Answer 1

我不能说我曾经见过或尝试过您正在尝试使用您的代码。本质上 ... Range1.Value = Range2.Find.Row

所以我不知道它是否真的应该工作。Find但是我可以告诉你，如果什么都不返回，你会得到一个错误。

与其将结果行分配给范围的默认属性，不如设置Set范围并测试范围是否为空。

Set Range1 = Range2.Find
If Not Range1 Is Empty Then
    Range1.Value = Range1.Row
Else
    'no match
End If

这可以防止错误并仍然返回该行。

此外，请注意合并的单元格，它们会影响您的结果。