问题描述
我有一个与 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)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！