VBA Testing two values, if one is different, copy

Question

I am having a fair amount of trouble with the code below:

Sub TestEmail()

    Dim i As Long
    Dim LastRow As Long
    Dim a As Worksheet
    Dim b As Worksheet
    Dim strText
    Dim ObjData As New MSForms.DataObject
    Set a = Workbooks("Book2").Worksheets(1)
    Set b = Workbooks("Book1").Worksheets(1)
    LastRow = a.Cells(Rows.Count, "A").End(xlUp).Row

    For i = 2 To LastRow
        If Not IsError(Application.Match(a.Cells(i, 7).Value, b.Columns(3), 0)) And IsError(Application.Match(a.Cells(i, 4).Value, b.Columns(11), 0)) Then
            a.Range("D" & i).Copy
            ObjData.GetFromClipboard
            strText = Replace(ObjData.GetText(), Chr(10), "")
            b.Range("K" & ).Value = b.Range("K" & ).Value & " / " & strText
        End If
    Next i

End Sub

I face two problems, one has me stumped and the other is due to lack of knowledge:

The line after IF is supposed to check if two values (numbers) in both workbooks match, and if two other values (text) don't match. If all true, then it must copy a value from Book2 and add it to a cell in book1.

The problems are:

-The macro doesn't seem to recognise when the values match or not.

-In the last line before "End If", I don't know how to tell excel to copy the text into the cell that didn't match in the second check.

I am sorry if I am not clear enough, this is hard to explain.

I'm hoping one of the experts knows how to make this work.

Thanks in advance

You are using `If Not condition 1 And condition 2`, so you are saying that if it doesn't match both conditions, Then you run the code. What you want to make are Nested If Statements https://stackoverflow.com/questions/34852883/vba-nested-if-statement However, one is `If` and the other `If Not` — danieltakeshi, Aug 14 '17 at 14:47
@danieltakeshi I changed to Nested If Statements and it seems to have done the trick for my first problem. Thank you very much! — PaulRey, Aug 15 '17 at 07:24
What is your objective on the 2nd problem? Could you give examples? What you are doing now is changing the value in Book1 "K#" with the value_from_Book1_cell/value_from_Book2_cell. And replacing the Blanks with Chr(10) — danieltakeshi, Aug 15 '17 at 11:44
Ok, let me explain more thoroughly. This macro runs on two workbooks, each with ID numbers and emails. The first If statement checks if the ID numbers match, if they do, the second IF statement checks if the emails match. If they don't the email from the first Workbook is added after the email of the second workbook. The problem here is that I don't know how to tell Excel where to copy the email. Hope that is clear enough. — PaulRey, Aug 15 '17 at 12:47
I think I found a small workaround using ActiveCell but I don't know how to perform a key action. Is it possible to activate the cell that was found not to match under `If IsError(Application.Match(a.Cells(i, 4).Value, b.Columns(11), 0)) Then`? — PaulRey, Aug 15 '17 at 14:34

danieltakeshi · Answer 1 · 2017-08-15T17:58:11.687

You are using If Not condition 1 And condition 2, so you are saying that if it doesn't match both conditions, Then you run the code. What you want to make are Nested If Statements However, one is If and the other If Not
To copy you are missing the i After "K"&: b.Range("K" & i) = b.Range("K" & i).Value & " / " & strText
The Address of the Cells are inside the Range Function, which in your case would be:

//It is the cell of the email from the first Workbook tou are copying, where you input the column D

a.Range("D" & i).Copy

//Add to Workbook b in column K the value from Cell K#/value copied

b.Range("K" & i) = b.Range("K" & i).Value & " / " & strText

You can also make it like this: b.Range("K" & i) = b.Range("K" & i).Value & " / " & a.Range("D" & i)

This way you are matching lines, so only if the IDs are on the same rows on both Workbooks it will work. If they aren't, you will have to use Nesting Loops or .Find Function

EDIT:

If I understood it, the code below might work if you make some changes for your application, because i didn't have the data to test and columns, etc. Try to implement it.

LastRowa = a.Cells(Rows.Count, "A").End(xlUp).Row
LastRowb = b.Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To LastRowa
    'Address of String to look for 
    LookForString = a.Worksheets(1).Cells(i, 4) '4 is the COLUMN_INDEX
    'Range to look on Workbook a
    With a.Worksheets(1).Range("D1:D" & LastRowa) 'choose column to look
        'Function .Find String on book a
     Set mail_a = .Find(LookForString, LookIn:=xlValues)
        If Not mail_a Is Nothing Then
        FirstAddress = mail_a.Address
            Do ' Actions here
               'Range to look on Workbook b
               With b.Worksheets(1).Range("K1:K" & LastRowb) 'choose column to look
                    'Function .Find on Workbook b
                   Set mail_b = .Find(LookForString, LookIn:=xlValues)
                     If Not mail_b Is Nothing Then
                     FirstAddress = mail_b.Address
                         Do 'Actions
                         'Verify if two other values (text) don't match
                         If Not WRITE_MATCH_CONDITION_HERE Then
                            'No need to verify of they are equal because the .Find function used the same reference
                            'I will use .Cells with .Row and .Column just to show another way to do it and make it dynamic
                            b.Cells(mail_b.Adress.Row, mail_b.Adress.Column) = b.Cells(mail_b.Adress.Row, mail_b.Adress.Column).Value & " / " & a.Cells(mail_a.Adress.Row, mail_a.Adress.Column) 'choose columns
                         End If
                     Set mail_b = .FindNext(mail_b)
                         Loop While Not mail_b Is Nothing And mail_b.Address <> FirstAddress
                     End If
               End With
                Set mail_a = .FindNext(mail_a)
            Loop While Not mail_a Is Nothing And mail_a.Address <> FirstAddress
        End If
    End With  
    Next i 
    End Sub

p.s.: The <> is missing on mail_a.Address <> FirstAddress and mail_b.Address <> FirstAddress, when i posted with

They are not within the same line unfortunately. This is what I've come up with in terms of **Nesting Loops**: `If Not IsError(Application.Match(a.Cells(i, 7).Value, b.Columns(3), 0)) Then If IsError(Application.Match(a.Cells(i, 4).Value, b.Columns(11), 0)) Then` I cannot seem to find a way to make the **.Find Function** do what I need, since I can't exactly tell it what to find. — PaulRey, Aug 15 '17 at 15:00

VBA Testing two values, if one is different, copy

1 Answers1