查找其他工作表(Excel)中是否存在值 [英] Find If Value Exists on other Worksheet (Excel)
问题描述
FeedSampleForm
Range(A5:B5)。值
存在于我的其他工作表 FeedSamples
的列 B
的任何位置。 可以帮助我吗?对于Excel来说,我几乎不是用户,这是我第一次使用它来开发。
编辑:
以下代码。这是针对不同的保存程序,具体取决于是否保存新记录或编辑之前创建的记录后保存。
对于样本数据,比方说我正在寻找FeedSampleForm .Range(A5:B5)。值为SR0238,我需要看到FeedSamples工作表中B列中是否存在SR0238,当前包含SR0237 - SR0252。如果不存在,我可以使用相同的代码作为我的添加记录功能,但如果这样做,我必须写入该确切行保存。
Sub SaveInspection()
如果modeAdd = True则
'Labeler Reg。 No.
工作表(FeedSamples)。Range(A1)。End(xlDown).Offset(1,0).value = Range(L3:M3)value
'Feed报告编号
工作表(FeedSamples)。Range(B1)。End(xlDown).Offset(1,0).value = Range(A5:B5)。value
'产品号/类号
工作表(FeedSamples)。范围(C1)。End(xlDown).Offset(1,0).value = Range(C5)。value
工作表(FeedSamples)。Range(E1)。End(xlDown).Offset(1,0).value = Range(D5)value
工作表(FeedSamples F1)。End(xlDown).Offset(1,0).value = Range(E5)。value
'说明编号
工作表(FeedSamples)。范围(H5 ).End(xlDown).Offset(1,0).value = Range(F5)。value
工作表(FeedSamples)。Range(I5)。End(xlDown).Offset 1,0).value = Range(G5)。value
工作表(FeedSamples)。Range(J5)。End(xlDown).Offset(1,0).value = Range H5)value
工作表(FeedSamples)。Range(K5)。End(xlDown).Offset(1,0).value = Range(I5)。value
' POSS essor No.
工作表(FeedSamples)。Range(L1)。End(xlDown).Offset(1,0).value = Range(J5:K5)value
'日期
工作表(FeedSamples)。Range(M)。End(xlDown).Offset(1,0).value = Range(L5:M5)。value
'
工作表(FeedSamples)。Range(AB1)。End(xlDown).Offset(1,0).value = Range(A8:F8)。value
'Possessor Address
工作表(FeedSamples)。Range(AC1)。End(xlDown).Offset(1,0).value = Range(A10:F10)value
'Possessor City / St
工作表(FeedSamples)。Range(AD1)。End(xlDown).Offset(1,0).value = Range(A11:E11)value
'POssessor Zipcode
工作表(FeedSamples)。Range(AE1)。End(xlDown).Offset(1,0).value = Range(F11)。value
'Labeler Name
工作表(FeedSamples)。范围(AF1)。End(xlDown).Offset(1,0).value = Range(H8:M8)。value
'Labeler地址
工作表(FeedSamples)。Range(AG1)。End(xlDown).Offset(1,0).value = Range(H1 0:M10)value
'Labeler City / St
工作表(FeedSamples)。Range(AH1)。End(xlDown).Offset(1,0).value = Range H11:L11)value
'Labeler Zipcode
工作表(FeedSamples)。Range(AI1)。End(xlDown).Offset(1,0).value = Range M11)值
'产品名称
工作表(FeedSamples)。Range(AJ1)。End(xlDown).Offset(1,0).value = Range(A13:I13 ).value
'1。 Med
工作表(FeedSamples)。Range(AK1)。End(xlDown).Offset(1,0).value = Range(J13:K13)value
' Non-Med
工作表(FeedSamples)。Range(AL1)。End(xlDown).Offset(1,0).value = Range(L13:M13)。value
'没有。手袋/禄。抽样
'总数保证
工作表(FeedSamples)。范围(P)。结束(xlDown).Offset(1,0).value =范围(C15 :E15)value
'标记样本
工作表(FeedSamples)。Range(Q)。End(xlDown).Offset(1,0).value = Range(F15: G15)。value
'Sample Def。
工作表(FeedSamples)。Range(R)。End(xlDown).Offset(1,0).value = Range(H15:I15)value
'
$ b'重复
'包标签标记或代码
工作表(FeedSamples)。范围(U)。结束(xlDown).Offset(1,0 ).value = Range(A17:H17)。value
'手上
工作表(FeedSamples)。Range(V)。End(xlDown).Offset(1,0) .value = Range(I17:K17)。value
'Approx。 Wt / Lbs
工作表(FeedSamples)。Range(W)。End(xlDown).Offset(1,0).value = Range(L17:M17)。value
'备注
工作表(FeedSamples)。Range(AA)。End(xlDown).Offset(1,0).value = Range(A19:M19)。value
'从
'样品方法
'Form
'探头尺寸
'商品编号/ b $ b工作表(FeedSamples)。Range(D)。End(xlDown).Offset(1,0).value = Range(A23:C23)value
modeAdd = False
End If
如果modeEdit = True然后
'在datatable中查找记录并保存在字段中。
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 on row& MATCH A5:B5)Value,sheet.Range(B:B),0))
'Range(O3)。Formula == IF(ISERROR(MATCH(12345,A: A,0)),未找到,在行上找到的值和MATCH(12345,A:A,0)))
result = Application.WorksheetFunction.VLookup(Range A5:B5)。value,sheet.Range(B2:B25000),2,False)
'抛出对象必需的错误
MsgBox结果
modeEdit = False
如果
End Sub
div class =h2_lin>解决方案
一个非常简单的方法是声明要搜索的范围和要查找的值。
Sub findValue()
Dim xlRange As Range
Dim xlCell As Range
Dim xlSheet As Worksheet
Dim valueToFind
valueToFind =MyValue
设置xlSheet = ActiveWorkbook.Worksheets(Sheet2)
设置xlRange = xlSheet.Range (B1:B10)
对于每个xlCell在xlRange
如果xlCell.Value = valueToFind然后
'做某事
结束如果
下一个xlCell
End Sub
我假设你的范围:B5)是一个合并的单元格,因为您指示它包含单个值。合并的单元格可以由合并中的左上单元格引用(或至少是我想像的)。所以你的合并范围(A5:B5)可以称为(A5)。无论如何,这里是上述方法的修改版本,更适合您的需要。
Sub findValue(ByVal valueToFind As String )
/ pre>
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
设置xlFormSheet = ActiveWorkbook.Worksheets(FeedSampleForm)
设置xlSamplesSheet = ActiveWorkbook.Worksheets(FeedSamples )
iLastRow = xlSamplesSheet.Range(B1)。End(xlDown).Row
设置xlRange = xlsamplesheet.Range(B1:B& iLastRow )
对于每个xlCell在xlRange
如果xlCell.value = valueToFind然后
bFound = True'< - 找到的值
iRow = xlCell.Row '< - 这是在
上找到该值的行End If
如果bFound然后退出对于'< - 可选:退出for循环一次找到值首次
下一个xlCell
End Sub
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 columnB
of my other worksheetFeedSamples
.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.
EDIT:
Current Code below. This is for different save procedures depending on if saving a new record or saving after editing a previously created record.
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
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屋!