Excel VBA交叉检查值 [英] Excel VBA to cross check value

问题描述

我有2张。



第一张是原始数据的输入



第二张是规格清单



sheet1

I have 2 sheets.

First sheet is the input of raw data

Second sheet is the list of specification

sheet1

a  3  3  3  3  3
b  2  2  2  2  2 

sheet2（spec）

sheet2 (spec)

a(upper) 5  5  5  5  5
a(lower) 1  1  1  1  1
b(upper) 4  4  4  4  4
b(lower) 3  3  3  3  3

所以它会在sheet1中显示通过和失败

so it will show pass and fail in sheet1

a  3  3  3  3  3  pass
b  2  2  2  2  2  fail

我尝试过：

What I have tried:

If Trim(FindString) <> "" Then
    With Sheets("Sheet2").Range("A:A") 'searches all of column A
        Set Rng = .Find(What:=FindString, _
                        After:=.Cells(.Cells.Count), _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
        If Not Rng Is Nothing Then
            

        Else
            

        End If
    End With
End If

推荐答案

您的查找将无法工作有两个原因



1. 之后：=。单元格（.Cells.Count）， - 您正在寻找工作表上最后一个单元格之后的内容< br $>


2. LookAt：= xlWhole，_ 表2中没有任何与单元格完全匹配的单元格工作表1中的值，因此要么 xlP art 或你的Findstring需要附加（上）和（下）位



所以你需要两次搜索或者假设（较低）范围紧跟在（上）范围之后，只需搜索 Cell.Value& （上）然后使用 rng.Offset（1,0）来获得更低的乐队



找到规范后，您需要比较每个列中的值。您可以使用 rng.Offset（0,1），rng.Offset（0,2）等，但更好的方法是返回其中包含spec值的数组，例如：

Your Find won't work for two reasons

1. After:=.Cells(.Cells.Count), - you are looking for stuff after the last cell on the sheet

2. LookAt:=xlWhole, _ you don't have any cells in sheet 2 that completely match the values in Sheet 1, so either that should be xlPart or your Findstring needs to have the "(upper)" and "(lower)" bits appended

So you need two searches OR assume that "(lower)" range is immediately after the "(Upper)" range and just search for Cell.Value & "(upper)" then use rng.Offset(1,0) to get the lower band

Once you have found your "spec" you will need to compare the values in each of the columns. You could use rng.Offset(0,1), rng.Offset(0,2) etc but better would be to return arrays with the spec values in them e.g.

Dim Arr As Variant
Arr = Range(rng.Address & ":"; rng.Offset(0, 5).Address).Value

你可以单步执行要验证的行上的单元格，然后将它们与上下数组中的值进行比较

You can then step through the cells on the row you are validating and just compare them to the values in the upper and lower arrays

这篇关于Excel VBA交叉检查值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！