时间戳

首页 > 解决方案 > 在搜索循环中设置变量列不工作，

excel - 在搜索循环中设置变量列不工作，

问题描述

我正在研究一个宏，该宏需要在“sheet1”的范围内查找每个值并在工作簿的所有工作表中搜索它（可能最多 7 个工作表），因为它需要太长时间，寻找值在整个工作表中，我想将每张工作表上的搜索字段减少到一个带有标题 IP 的变量列。

这是我到目前为止的宏，但我不能让它只在指定的范围内工作，但是，删除这些行会使宏工作正常。

先感谢您。

Sub findInventory()
Dim ws As Worksheet
Dim strWhat, rngFound, mString As String
Dim rngSearch, osfind, rfind, rfcol As Range
Dim i, x As Integer
Dim LastRow, oscol, lcol, e, lrowA, remChar, fcol As Long




Sheets("GVM Report").Cells(1, 1).Offset(0, 1).Resize(, 2).EntireColumn.Insert
Sheets("GVM Report").Cells(1, 1).Offset(0, 1).Value = "INVENTORY"
Sheets("GVM Report").Cells(1, 1).Offset(0, 2).Value = "OPSDB"

Set rfind = ActiveWorkbook.Sheets("GVM Report").Rows("1:3").Find(What:="IP", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
lcol = rfind.Column

Set osfind = ActiveWorkbook.Sheets("GVM Report").Rows("1:3").Find(What:="OS*", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
oscol = osfind.Column

LastRow = Sheets("GVM Report").Range("A" & Rows.Count).End(xlUp).Row

For x = 2 To LastRow

strWhat = Sheets("GVM Report").Cells(x, lcol)
    For Each ws In Worksheets
    Set rfcol = ws.Rows("1:3").Find(What:="IP", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
    fcol = rfcol.Column
    With ws.Columns(fcol)
    Select Case ws.name
    Case "Operations", "Data", "FYI all OS", "Unique Values", "GVM Report"

    Case Else
       Set rngSearch = ws.Cells.Find(What:=strWhat)
        If strWhat <> "" Then
            If Not rngSearch Is Nothing Then
                i = i + 1
                If i = 1 Then
                    rngFound = rngSearch.Worksheet.name
                Else
                    rngFound = rngFound & " | " & rngSearch.Worksheet.name
                End If
                    End If

        End If

                Sheets("GVM Report").Cells(x, 2) = rngFound


    End Select
    End With
    Next ws
    rngFound = ""
    i = 0
Next x
End Sub

标签： excelvbaloopsfind

解决方案

我不确定我是否可以复制你的错误，但我已经整理了你的代码，它可能对你有用，所以这里是：

Sub findInventory()
Dim ws As Worksheet
Dim wsGVM As Worksheet: Set wsGVM = ThisWorkbook.Sheets("GVM Report")
Dim strWhat As String, rngFound As String, mString As String
Dim rngSearch As Range, osfind As Range, rfind As Range, rfcol As Range
Dim i As Integer, x As Long, LastRow As Long, oscol As Long, lcol As Long, fcol As Long

wsGVM.Cells(1, 1).Offset(0, 1).Resize(, 2).EntireColumn.Insert
wsGVM.Cells(1, 1).Offset(0, 1).Value = "INVENTORY"
wsGVM.Cells(1, 1).Offset(0, 2).Value = "OPSDB"

Set rfind = wsGVM.Rows("1:3").Find(What:="IP", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
If Not rfind Is Nothing Then lcol = rfind.Column

Set osfind = wsGVM.Rows("1:3").Find(What:="OS*", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
If Not osfind Is Nothing Then oscol = osfind.Column

LastRow = wsGVM.Range("A" & Rows.Count).End(xlUp).Row

For x = 2 To LastRow
    strWhat = wsGVM.Cells(x, lcol)
    For Each ws In Worksheets
        Set rfcol = ws.Rows("1:3").Find(What:="IP", LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
        If Not rfcol Is Nothing Then
            fcol = rfcol.Column
            With ws.Columns(fcol)
                Select Case ws.Name
                    Case "Operations", "Data", "FYI all OS", "Unique Values", "GVM Report"
                    ''''
                    Case Else
                        If strWhat <> "" Then
                            Set rngSearch = .Find(What:=strWhat)
                            If Not rngSearch Is Nothing Then
                                i = i + 1
                                If i = 1 Then
                                    rngFound = rngSearch.Worksheet.Name
                                Else
                                    rngFound = rngFound & " | " & rngSearch.Worksheet.Name
                                End If
                            End If
                        End If
                        wsGVM.Cells(x, 2) = rngFound
                End Select
            End With
        End If
    Next ws
    rngFound = ""
    i = 0
Next x
End Sub

推荐阅读