VBA 4 conditions have to match if matched then write the word "TBC" into desired cell.

[ExcelRookie17]

Hello,

I have been trying for a few days to figure this out, however I have run out of solutions and formulas to complete this. I have two sheets and would like to find the value from sheet1 column 1 in sheet2 column 3 and also sheet1 column 2 in sheet2 column 11.

If all matches then I want it to write TBC in Sheet2 Column 2.

I have the following code but I cannot get around it to match all 4 and then actually paste "TBC" value in the cells.

lastrow = Thisworkbook.worksheets("Sheet1").cells(Rows.Count, 1).End(xlUp).row

For i = lastrow to 2 Step by -1

If thisworkbook.worksheets("Sheet1").Cells(i,1).Value = Thisworkbook.worksheets("Sheet2").Cells(i,3).Value = True Then

Worksheets("Sheet2").Cells(i,2).Value = "TBC"

End If

Next

End Sub

Can someone kindly help me please....

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of your two sheets . Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[ExcelRookie17]

Hello mumps,

please find below the link for it, it is wetransfer same as dropbox or box just no sign up etc necessary.

Book1.xlsx

1 file sent via WeTransfer, the simplest way to send your files around the world

we.tl

So in the file I have made an example of two sheets.
First condition
I would like a VBA Userform Macro to match the Account number (Column 1) on sheet "Accounts" Column 2 to the Account number (Column 3) on sheet "Risk".
Second condition
"Country"(Column 2) on sheet Accounts should also match "Risk Location" (Column 11) on sheet Risks.

If both conditions are met then

Paste the phrase "TBC" to Column 2 on Sheet Risks.

It sounds so simple when I write it out cannot believe I am incapable of writing this piece of vba code man.....I am so disappointed

Thank you so much for your time and help with this

 

[ExcelRookie17]

@mumps

 

[ExcelRookie17]

Column 2

please disregard this.

 

[mumps]

Try:

Sub CompareData()
    Application.ScreenUpdating = False
    Dim v1 As Variant, v2 As Variant, srcWS As Worksheet, desWS As Worksheet, i As Long, dic As Object, Val As String, lRow As Long
    Set srcWS = Sheets("Accounts")
    Set desWS = Sheets("Risks")
    lRow = desWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Set srcRng = Range("A2", Range("A" & Rows.Count).End(xlUp))
    v1 = srcWS.Range("A2", srcWS.Range("B" & Rows.Count).End(xlUp)).Value
    v2 = desWS.Range("A2:K" & lRow).Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = 1 To UBound(v1)
        Val = v1(i, 1) & "|" & v1(i, 2)
        If Not dic.Exists(Val) Then
            dic.Add Val, Nothing
        End If
    Next i
    For i = 1 To UBound(v2)
        Val = v2(i, 3) & "|" & v2(i, 11)
        If dic.Exists(Val) Then
            v2(i, 2) = "TBC"
        End If
    Next i
    desWS.Range("A2").Resize(UBound(v2), 11) = v2
    Application.ScreenUpdating = True
End Sub

 

[ExcelRookie17]

@mumps

Many thanks for the piece of code !!!! It really works and I would have not being able to do this without you....

I have one more question, just for future reference.

If I want to add one more condition to the code what do I need to consider ?

Regards

ExcelRookie17

 

[mumps]

You are very welcome. The answer to your question would be rather too lengthy to explain here, particularly since you would need to have an understanding of how dictionaries and arrays work. You could do some research on those two topics to get some understanding. If you upload a revised version of your workbook that includes the additional condition, I could modify the macro accordingly and you would be able to compare the two macros.