Excel VBA to cross check value

Question

0.00/5 (No votes)

See more:

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)

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

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

Posted 4-Apr-19 23:01pm

JiaWei Lee

Updated 5-Apr-19 1:49am

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2019-04-05T01:50:00

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