查找其他工作表上是否存在值 (Excel) [英] Find If Value Exists on other Worksheet (Excel)

问题描述

我有一个与 Excel 工作表上的按钮单击事件相关联的宏.当此事件触发时，我需要查看我的工作表 FeedSampleForm Range("A5:B5").Value 上的值是否存在于 B 我的其他工作表 FeedSamples.

I have a macro tied to a button click event on my Excel worksheet. When this event fires, I need to see if the value on my worksheet FeedSampleForm Range("A5:B5").Value exists anywhere in column B of my other worksheet FeedSamples.

谁能帮我解决这个问题?我几乎不是 Excel 的用户，这是我第一次使用 Excel 进行开发".

Can anyone assist me with this? I'm barely a user when it comes to Excel, and this is my first time "Developing" with it.

编辑:

下面的当前代码.这是针对不同的保存过程，具体取决于是保存新记录还是在编辑以前创建的记录后保存.

Current Code below. This is for different save procedures depending on if saving a new record or saving after editing a previously created record.

对于示例数据，假设我正在寻找FeedSampleForm".Range(A5:B5").值是SR0238"，我需要查看SR0238"是否存在于FeedSamples"工作表的 B 列中，当前包含SR0237"-SR0252".如果它不存在，我可以使用与我的添加记录功能相同的代码，但如果存在，我必须在保存时写入该行.

For Sample Data, say I'm looking for "FeedSampleForm".Range("A5:B5").Value which is "SR0238", I need to see if "SR0238" exists in column B on "FeedSamples" worksheet, currently containing "SR0237" - "SR0252". If it doesn't exist, I can use same code as my Add Record functionality, but if it does, I have to write to that exact row when saving.

Sub SaveInspection()
    If modeAdd = True Then
    'Labeler Reg. No.
    Worksheets("FeedSamples").Range("A1").End(xlDown).Offset(1, 0).value = Range("L3:M3").value
    'Feed Report No.
    Worksheets("FeedSamples").Range("B1").End(xlDown).Offset(1, 0).value = Range("A5:B5").value
    'Product No. / Class No.
    Worksheets("FeedSamples").Range("C1").End(xlDown).Offset(1, 0).value = Range("C5").value
    Worksheets("FeedSamples").Range("E1").End(xlDown).Offset(1, 0).value = Range("D5").value
    Worksheets("FeedSamples").Range("F1").End(xlDown).Offset(1, 0).value = Range("E5").value
    'Description No.
    Worksheets("FeedSamples").Range("H5").End(xlDown).Offset(1, 0).value = Range("F5").value
    Worksheets("FeedSamples").Range("I5").End(xlDown).Offset(1, 0).value = Range("G5").value
    Worksheets("FeedSamples").Range("J5").End(xlDown).Offset(1, 0).value = Range("H5").value
    Worksheets("FeedSamples").Range("K5").End(xlDown).Offset(1, 0).value = Range("I5").value
    'Possessor No.
    Worksheets("FeedSamples").Range("L1").End(xlDown).Offset(1, 0).value = Range("J5:K5").value
    'Date
    Worksheets("FeedSamples").Range("M").End(xlDown).Offset(1, 0).value = Range("L5:M5").value
    'Possessor Name
    Worksheets("FeedSamples").Range("AB1").End(xlDown).Offset(1, 0).value = Range("A8:F8").value
    'Possessor Address
    Worksheets("FeedSamples").Range("AC1").End(xlDown).Offset(1, 0).value = Range("A10:F10").value
    'Possessor City/St
    Worksheets("FeedSamples").Range("AD1").End(xlDown).Offset(1, 0).value = Range("A11:E11").value
    'POssessor Zipcode
    Worksheets("FeedSamples").Range("AE1").End(xlDown).Offset(1, 0).value = Range("F11").value
    'Labeler Name
    Worksheets("FeedSamples").Range("AF1").End(xlDown).Offset(1, 0).value = Range("H8:M8").value
    'Labeler Address
    Worksheets("FeedSamples").Range("AG1").End(xlDown).Offset(1, 0).value = Range("H10:M10").value
    'Labeler City/St
    Worksheets("FeedSamples").Range("AH1").End(xlDown).Offset(1, 0).value = Range("H11:L11").value
    'Labeler Zipcode
    Worksheets("FeedSamples").Range("AI1").End(xlDown).Offset(1, 0).value = Range("M11").value
    'Product Name
    Worksheets("FeedSamples").Range("AJ1").End(xlDown).Offset(1, 0).value = Range("A13:I13").value
    '1. Med
    Worksheets("FeedSamples").Range("AK1").End(xlDown).Offset(1, 0).value = Range("J13:K13").value
    '2. Non-Med
    Worksheets("FeedSamples").Range("AL1").End(xlDown).Offset(1, 0).value = Range("L13:M13").value
'No. Bags/Loc. Sampled

    'Total No. Guarantees
    Worksheets("FeedSamples").Range("P").End(xlDown).Offset(1, 0).value = Range("C15:E15").value
    'Flag Sample
    Worksheets("FeedSamples").Range("Q").End(xlDown).Offset(1, 0).value = Range("F15:G15").value
    'Sample Def.
    Worksheets("FeedSamples").Range("R").End(xlDown).Offset(1, 0).value = Range("H15:I15").value
    'Compliance

    'Duplicate

    'Bag Tag Mark or Code
    Worksheets("FeedSamples").Range("U").End(xlDown).Offset(1, 0).value = Range("A17:H17").value
    'On Hand
    Worksheets("FeedSamples").Range("V").End(xlDown).Offset(1, 0).value = Range("I17:K17").value
    'Approx. Wt/Lbs
    Worksheets("FeedSamples").Range("W").End(xlDown).Offset(1, 0).value = Range("L17:M17").value
    'Remarks
    Worksheets("FeedSamples").Range("AA").End(xlDown).Offset(1, 0).value = Range("A19:M19").value
    'Sample Taken From

    'Sample Method

    'Form

    'Probe Size

    'Product No./Class No.
    Worksheets("FeedSamples").Range("D").End(xlDown).Offset(1, 0).value = Range("A23:C23").value

    modeAdd = False
End If

If modeEdit = True Then
    'find the record in "datatable" and save over fields.
    Dim result As Variant
    Dim sheet As Worksheet
    Set sheet = ActiveWorkbook.Sheets("FeedSamples")

    'Range("O3").Formula = "=IF(ISERROR(MATCH(Range("A5:B5").Value, sheet.Range("B:B"), 0)), "Not Found", "Value found on row " & MATCH(Range("A5:B5").Value, sheet.Range("B:B"), 0))"
    'Range("O3").Formula = "=IF(ISERROR(MATCH(12345,A:A,0)),"Not Found","Value found on row " & MATCH(12345,A:A,0)))"

    result = Application.WorksheetFunction.VLookup(Range("A5:B5").value, sheet.Range("B2:B25000"), 2, False)
    'Throws Object Required Error
    MsgBox result
    modeEdit = False
    allowNav = True

End If


End Sub

推荐答案

一个非常简单的方法是声明要搜索的范围和要查找的值.

A very easy way is to declare the range that you want to search in and the value that you want to find.

Sub findValue()

    Dim xlRange As Range
    Dim xlCell As Range
    Dim xlSheet As Worksheet
    Dim valueToFind

    valueToFind = "MyValue"
    Set xlSheet = ActiveWorkbook.Worksheets("Sheet2")
    Set xlRange = xlSheet.Range("B1:B10")

    For Each xlCell In xlRange
        If xlCell.Value = valueToFind Then
            'Do Something
        End If
    Next xlCell

End Sub

我假设您的 ("A5:B5") 范围是一个合并的单元格，因为您指出它包含一个值.合并的单元格只能由合并中的左上角"单元格引用(或者至少我是这么认为的).因此，您的 ("A5:B5") 的合并范围可以简称为 ("A5").无论如何，这里是上述方法的修改版本，更适合您的需求.

I'm assuming that your range of ("A5:B5") is a merged cell because you indicated that it contained a single value. Merged cells can just be referenced by the "top left" cell within the merge (or at least that's how I think of it). So your merged range of ("A5:B5") can be referred to as just ("A5"). Anyway, here is a modified version of the method above that is more suited for your needs.

Sub findValue(ByVal valueToFind As String)

    Dim xlRange As Range
    Dim xlCell As Range
    Dim xlFormSheet As Worksheet
    Dim xlSamplesSheet As Worksheet
    Dim iLastRow As Integer
    Dim iRow As Integer
    Dim bFound As Boolean

    bFound = False
    Set xlFormSheet = ActiveWorkbook.Worksheets("FeedSampleForm")
    Set xlSamplesSheet = ActiveWorkbook.Worksheets("FeedSamples")

    iLastRow = xlSamplesSheet.Range("B1").End(xlDown).Row

    Set xlRange = xlsamplesheet.Range("B1:B" & iLastRow)

    For Each xlCell In xlRange
        If xlCell.value = valueToFind Then
            bFound = True '<-- The value was found
            iRow = xlCell.Row '<-- Here is the row that the value was found on
        End If

        If bFound Then Exit For '<-- Optional: Exit the for loop once the value is found the first time
    Next xlCell

End Sub

这篇关于查找其他工作表上是否存在值 (Excel)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！