如何在另一个工作表中查找值并返回小于X的值? [英] How to find the value in another sheet and return smaller than X?

问题描述

我必须编写代码来查找城市并返回小于我选择的距离.例如，在sheet1中，单元格A2是城市名称，在单元格A3中是距离.在sheet2中是城市及其距离的列表:

I have to write a code that will find the city and return the distance smaller than I chose. For example, in sheet1 cell A2 is a city name, in cell A3 is a distance. In sheet2 are list of cities and their distance.:

我希望在第1行中找到城市(从Sheet1单元格A2中获取)，并仅返回距离值小于sheet1单元格A3中的值的城市，国家和距离.

I want that in row 1 will find the city (from Sheet1 cell A2) and return only cities, countries and distance which distance values is smaller than value in sheet1 cell A3.

我已经尝试过此代码，但不确定下一步该怎么做:

I have try this code but I am not sure what I should do next:

Dim Rng_Header As Range: Set Rng_Header = Sheets("Sheet2").[d1:h1]
Dim Ws1 As Worksheet: Set Ws1 = Sheets("Sheet1")
Dim index_column As Variant
   index_column = Application.Match(Ws1.[a2], Rng_Header, 0)    'find index column in Rng_Header

谢谢您的帮助

推荐答案

请研究以下代码.你会很开心的.此外，它还能满足您的需求.

Please study the code below. You will have fun. Besides, it also does what you want.

Sub ListNearerCities()

    Const Target As String = "D2"           ' place the output there (on Sheet1)

    Dim Fun As Variant                      ' output array
    Dim n As Integer                        ' Fun index counter
    Dim Ws As Worksheet
    Dim City As String                      ' Value of A2
    Dim Distance As Long                    ' value of A3
    Dim WsData As Worksheet
    Dim Data As Variant
    Dim Rng As Range
    Dim R As Long, C As Long                ' Row / Column

    Set Ws = Worksheets("Sheet1")
    With Ws
        City = .Cells(2, "A").Value
        Distance = .Cells(3, "A").Value
        With .Range(Target).Resize(1, 3)
            ' clear & reset the output area
            .EntireColumn.ClearContents
            With .Offset(-1)
                .Value = Split("City Country Distance")
                .Font.Bold = True
            End With
        End With
    End With

    Set WsData = Worksheets("Sheet2")
    With WsData
        On Error Resume Next
        Set Rng = .Range(.Cells(1, 4), .Cells(1, .Columns.Count).End(xlToLeft))
        C = Application.Match(City, Rng, 0)     'find index column among column captions
        If Err Then
            MsgBox """" & City & """ isn't listed.", _
                   vbInformation, "No data available"
            Exit Sub
        End If

        C = C + 3       ' convert Rng column to Sheet column
        Set Rng = .Range(.Cells(1, "A"), .Cells(.Rows.Count, C).End(xlUp))
        Data = Rng.Value

        ReDim Fun(1 To 3, 1 To UBound(Data))
        For R = 2 To UBound(Data)
            If Distance > Val(Data(R, C)) Then
                If (Val(Data(R, C)) > 0) And (City <> Data(R, 3)) Then
                    n = n + 1
                    Fun(1, n) = Data(R, 1)
                    Fun(2, n) = Data(R, 3)
                    Fun(3, n) = Data(R, C)
                End If
            End If
        Next R
    End With

    If n Then
        ReDim Preserve Fun(1 To 3, 1 To n)
        Ws.Range(Target).Resize(UBound(Fun, 2), UBound(Fun)).Value = Application.Transpose(Fun)
        ' re-use of obsolete string variable
        City = n & " record" & IIf(n = 1, " was", "s were")
    Else
        City = "No data matching the criteria was"
    End If

    MsgBox City & " found.", vbInformation, "Search report"
End Sub

这篇关于如何在另一个工作表中查找值并返回小于X的值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！