Need tScript Needs to Skip Names If They are Not Present in Both Worksheets

**Fyre182** · 03-06-2010, 06:16 PM

So, as a preface, I am a complete novice at excel. I recently attended a small vba seminar for excel and realized that vba could eliminate a lot of headache with my research. The problem is that I have little to no knowledge of programing, so I've been working at this for awhile now. Any help would be appreciated.

I'm attempting to write a program that will analyze names (that also have numbers in them) in one worksheet. We can call this Worksheet A -- the names are all in column A. I would like the program to then be able to search a second worksheet, worksheet B, and find the matching name. The name will also be in column A on worksheet B. I would then like the program to look at the data that is adjacent to the name in the row direction -- horizontally to the right and determine if the value is greater or smaller than .9 (greater than 0.9 in the positive direction or greater than -0.9 in the negative direction). If both the adjacent values next to the name in worksheets A and B meet the criteria, i.e. only if they are both greater than .9 or smaller than -.9 then I would like the program to output the name to a third worksheet -- worksheet c. It would also be lovely if it could output the associated numbers into the third worksheet, but I believe that will be way too difficult for me to do.

I have started to write something that Would find a name and then put it onto a third sheet. But, again, its a very elementary attempt, and I'm begining to believe that this is well beyond my capabilities.

Any pointers in the write direction or help would be greatly appreciated.

Sub DataCrunch()
    Sheets("A").Select
    Range("A1").Select
    
    'this is my counter . . . count count count'
    Dim Counter As Integer
    Counter = 1
    
    'this is my checker for name'
    Dim Checker As Boolean
    Checker = False
    
    Dim TestOne As String
    TestOne = ActiveCell.Value
    
    Dim TestOneTrue As String
    
    
    Sheets("B").Select
    Range("A1").Select
    
    Do While (Checker = False)

        If (TestOne = ActiveCell.Value) Then
        
        TestOneTrue = ActiveCell.Value
        Sheets("C").Select
        Range("A1").Value = TestOneTrue
        Checker = True
        
        Else
        Sheets("B").Select
        ActiveCell.Offset(1, 0).Select
        
        End If
    
    Loop
    
    
End Sub

that is what i have done

The file associated with that is attached.

**StephenR** · 03-06-2010, 06:30 PM

Does this do what you want?

Sub x()
  
Dim rFind As Range, rCell As Range
 
For Each rCell In Sheets("A").Range("A1", Sheets("A").Range("A1").End(xlDown))
    Set rFind = Sheets("A").Range("A1", Sheets("A").Range("A1").End(xlDown)).Find(What:=rCell, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
    If Not rFind Is Nothing Then
        If Abs(rCell.Offset(, 1)) >= 0.9 And Abs(rFind.Offset(, 1)) >= 0.9 Then
            With Sheets("C").Cells(Rows.Count, 1).End(xlUp)(2)
                .Value = rCell
                .Offset(, 1).Value = rCell.Offset(, 1)
                .Offset(, 2).Value = rFind.Offset(, 1)
            End With
        End If
    End If
Next rCell

End Sub

**shg** · 03-06-2010, 08:45 PM

Welcome to the forum, Fyre.

Please take a few minutes to read the forum rules, and then change your QUOTE tags to CODE tags.

Thanks.

**Fyre182** · 03-06-2010, 09:17 PM

Welcome to the forum, Fyre.

Please take a few minutes to read the forum rules, and then change your QUOTE tags to CODE tags.

Thanks.

Sorry about that. I'll be sure not to forget.

Does this do what you want?

Thank you so much. I didn't expect to get this much help. But, no, that doesn't quite do what I need but its definitely a start in the right direction. Thank you very very much.

I just tried the script and it seems to be pulling any name that has a value greater than .9 or less than -.9 associated with it. Instead, I only want names with values that satisfy being greater than .9 or less than -.9 in both worksheets a and b. So in this example at423h.1 should not make the list on C, since on worksheet b it is only -.2 and not -.9 or less as in -1, -1.1 etc.

at423h.1 is valued at -1.2 on worksheet A which should allow it to be on the final worksheet with the associated value, but since on worksheet b its value is only -.2, in the end, it should not make it

It also seems that on the final sheet-- worksheet c, the values in column c are just repeats of column b, instead of the values associated with the name on sheet b.

I'm sure this is very confusing. Thank you again for your response.

**ravishankar** · 03-07-2010, 05:01 AM

Hi
Run the macro "compare" in the attached workbook and see if the output in sheet C matches your expectation
ravi

**royUK** · 03-07-2010, 05:06 AM

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

**royUK** · 03-07-2010, 05:06 AM

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

**Fyre182** · 03-09-2010, 09:37 AM

I am extremely sorry. I have made corrections, I hope that the post now complies with the rules.

Is there a way that using the code that ravishankar provided can have the script simply make note if a name is present on one list and not on another.

For example, if the name jimbob was present on the first worksheet and not present on the second worksheet. Instead of the program spitting out an error and stopping. Is there a way to make the program simply make a note in sheet c and then go on?

Sub List()
Dim x As Long, y As Long, d As Long, a As Long
Sheets("a").Select
Sheets("c").Range("A:C").ClearContents
d = 2
x = Sheets("A").Range("A" & Rows.Count).End(xlUp).Row
y = Sheets("B").Range("A" & Rows.Count).End(xlUp).Row
MsgBox x & y
For a = 2 To x
Sheets("A").Cells(1, 26) = "=match(A" & a & ",B!A1:A" & y & ",0)"
Sheets("A").Cells(1, 27) = "=iserror(z1)"

If Sheets("A").Cells(1, 27) = False Then
c = Sheets("A").Cells(1, 26)
If Abs(B!Cells(c, 2)) > 0.9 And Abs(a!Cells(a, 2)) > 0.9 Then
Sheets("C").Cells(d, 1) = Sheets("A").Cells(a, 1)
Sheets("C").Cells(d, 2) = Sheets("A").Cells(a, 2)
Sheets("C").Cells(d, 3) = Sheets("B").Cells(, 2)
d = d + 1
End If
End If
Next a
Sheets("A").Range("Z1:AA1").ClearContents
MsgBox "Complete"
End Sub
Sub compare()
Dim x As Long, y As Long, d As Long, a As Long
Sheets("c").Range("A:C").ClearContents

Sheets("B").Select
d = 2
x = Sheets("A").Range("A" & Rows.Count).End(xlUp).Row
y = Sheets("B").Range("A" & Rows.Count).End(xlUp).Row
For a = 1 To y
Sheets("B").Cells(a, 3) = "=Vlookup(A" & a & ",A!A1:B" & x & ",2,false)"
If Abs(Sheets("B").Cells(a, 3)) > 0.9 And Abs(Sheets("B").Cells(a, 2)) > 0.9 Then
Sheets("C").Cells(d, 1) = Sheets("B").Cells(a, 1)
Sheets("C").Cells(d, 2) = Sheets("B").Cells(a, 2)
Sheets("C").Cells(d, 3) = Sheets("B").Cells(a, 3)
d = d + 1
End If
Next a
Sheets("A").Range("Z1:AA1").ClearContents
MsgBox "Complete"
End Sub

re-uploaded sheet with some names and values that appear on one sheet but not on the other.

thank you again to anyone who helps. Even a pointer in the right direction is helpful

thank you

**StephenR** · 03-09-2010, 10:16 AM

I made a very simple mistake before. I think below should work.

Sub x()
  
Dim rFind As Range, rCell As Range
 
For Each rCell In Sheets("A").Range("A1", Sheets("A").Range("A1").End(xlDown))
    Set rFind = Sheets("B").Range("A1", Sheets("B").Range("A1").End(xlDown)).Find(What:=rCell, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
    If Not rFind Is Nothing Then
        If Abs(rCell.Offset(, 1)) >= 0.9 And Abs(rFind.Offset(, 1)) >= 0.9 Then
            With Sheets("C").Cells(Rows.Count, 1).End(xlUp)(2)
                .Value = rCell
                .Offset(, 1).Value = rCell.Offset(, 1)
                .Offset(, 2).Value = rFind.Offset(, 1)
            End With
        End If
    End If
Next rCell

End Sub