VBA-嵌套循环以查找不同电子表格中一列的每个值? [英] VBA - nested loop to find each value of a column in a different spreadsheet?

问题描述

Sub Search2 () 
Dim endRowsl As Long
endRowsl = Sheets ("Orders").Cells.Rows.Count, "A").End(xlUp).Row 
Dim countRows4 As Integer
countRows4 = 4
Dim x1Range As Range
Dim xlCell As Range
Dim xlSheet As Worksheet
Dim keyword As String
Set xlSheet = Worksheets ("Tag50")
Set x1Range = xlSheet.Range ("Al :A5") 

For j = 2 To endRowsl
keyword = Sheets("Order").Range("B" & j ).Value 
For Each xlCell In x1Range
    If xlCell.Value = keyword Then 
        Next xlCell 
    ElseIf Not xlCell.Value = keyword Then
        Sheets ("Test").Rows(countRows4).Value = Sheets("Order").Rows(j).Value
        countRows4 = countRows4 + 1
        Next xlCell 
    End If 
Next  
End Sub

我现在所拥有的没有给我任何东西.我相信我的逻辑是正确的，但是我的语法是错误的?

What I have right now that is not giving me anything. I believe my logic is correct, but my syntax is not?

第一次在VBA上.我试图遍历第一张纸的订单"以在第二张纸的B列中找到每个值.如果该值不存在，我需要将工作表1中的A列值与工作表3中的相同值匹配，然后返回工作表3的B列中的值.我了解其背后的逻辑，但不确定如何编写VBA代码.我已经张贴了我在这里的东西.

First time at VBA. I am trying to loop through the first sheet 'orders' to find each value in column B in the second sheet. If the value is NOT there, I need to match the column A value in sheet 1 to the same value in sheet 3, then return the value in column B of sheet 3. I understand the logic behind it, but I am not sure how to write the VBA code. I have posted what I have here.

感谢您提供有关语法，逻辑，格式等方面的任何帮助

Any help on syntax, logic, format, etc., is appreciated

推荐答案

这是一个可能的解决方案

here's a possible solution

Option Explicit

Sub main()
    Dim orderRng As Range, tag50Rng As Range, sheet3Rng As Range, testRng As Range
    Dim cell As Range, found As Range
    Dim testRowsOffset As Long

    Set orderRng = GetRange("orders", "B", 2) '<--| set sheet "order" column "B" cells from row 2 down to last non empty one as range to seek values of in other ranges
    Set tag50Rng = GetRange("tag50", "A") '<--| set sheet "tag50" column "A" cells from row 1 down to last non empty one as range where to do 1st lookup in
    Set sheet3Rng = GetRange("sheet3", "A") '<--| set sheet "sheet3" column "A" cells from row 1 down to last non empty one as range where to do 2nd lookup in
    Set testRng = Worksheets("test").Range("A4") '<--| set sheet "test" cell "A4" as range where to start returning values from downwards

    For Each cell In orderRng '<--| loop through each cell of "order" sheet column "B"
        Set found = tag50Rng.Find(what:=cell.Value, lookat:=xlWhole, LookIn:=xlValues) '<--| lookup for current cell value in "tag50" column "A"

        If found Is Nothing Then '<--| if no match found
            Set found = sheet3Rng.Find(what:=cell.Offset(, -1).Value, lookat:=xlWhole, LookIn:=xlValues) '<--| lookup for current cell offsetted 1 column left value in "sheet3" column "A"
            If Not found Is Nothing Then '<--| if match found
                testRng.Offset(testRowsOffset) = found.Offset(, 1).Value '<--| return sheet3 found cell offsetted 1 column right value
                testRowsOffset = testRowsOffset + 1 '<--| update row offset counter from "test" cell A4
            End If
        End If
    Next cell
End Sub


Function GetRange(shtName As String, col As String, Optional firstRow As Variant) As Range
    ' returns the range of the passed worksheet in the passed column from passed row to last non empty one
    ' if no row is passed, it starts from row 1

    If IsMissing(firstRow) Then firstRow = 1
    With Worksheets(shtName)
        Set GetRange = .Range(.Cells(1, col), .Cells(.Rows.Count, col).End(xlUp))
    End With
End Function

根据需要更改所有相关参数(工作表名称，要查找的列和从中开始的行)

change all relevant parameters (sheet names, their columns to lookup in and rows to start from) as per your needs

这篇关于VBA-嵌套循环以查找不同电子表格中一列的每个值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！