VBA - Compare 2 excel columns.

[CookieMaster]

Hello, I have something a bit different today. 

 

For my simplicity at work and for more experience I want write something that does the following

 

Scan and compare column a and f and see if a match exists. (these wont be on same rows) if a match exists grab the info off of column b c d (same row of match in column a ) and through it into an email.

 

I have started it a bit already but not sure how to compare the rows, I am new to vba.. not vb though. I imagine more variables will be needed. 

 

 

Sub SendSpec()

    Dim OutApp As Object
    Dim OutMail As Object
    Dim Body As String
    Dim SpecID As String 'Column A
    Dim DodgeID As String ' Column F
    Dim ProjectName As String ' Column B
    Dim Location As String ' Coulumn C
    Dim Architect As String ' Column D
    
   'Go through whole column and compare SpecColumn (A) to DodgeColumn (F) and see if matches exists, note that rows will not match.

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    Body = "Hello, this is an automated message." & vbNewLine & vbNewLine & _
              "The following spec is bidding: " & vbNewLine & _
              "Dodge ID: " & vbNewLine & _
              "Project Name: " & vbNewLine & _
              "Location: " & vbNewLine & _
              "Architect: "

    On Error Resume Next
    With OutMail
        .To = "test@test.com"
        .CC = ""
        .BCC = ""
        .Subject = "A Spec is Now Bidding"
        .Body = Body
        .Display '.Send
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

 

[Nuluvius]

It seems like you are taking a sledgehammer to a nail. One can easily create a formula for comparing columns and rows. The email could be triggered as a result of that instead as it seems a far cleaner and simpler approach.

[Cdpopham]

Still need help on this? The syntax is almost identical to VB.NET.

 

First, Wrap that email code in a function, with all those strings variables you have as inputs. Then just iterate through the rows and find matches. 

 

Note: Cells(0,0).Value is 0 based. It is (column,row), so (0,1) would be column A,row 2

 

Dim i as Integer;

Dim j as Integer

For i = 1 To Rows.Count
   For j = 1 to Rows.Count

      If Cells(1, i).Value = Cells(3,j),Value Then

        //Send the values of column whatever, row J, to the code you have to send email

         SendEmail(Cells(3,j).Value)

      End If

   Next
Next i