CODEWITHSUNDEEP
vbaexcelif-statement
gonvart
gonvart

Reputation: 11

If Then Statement

I have a VBA code on loop and now I've come to deadstill because I cant figure out how to check something. I have a command button then when clicked I want it to perform an if then statement.

Basically If cell F5 says "Not in List" Then make the customer ID value +1 and start over, If not then start sub loop 1

Any suggestions? Thanks

UPDATE:

Here is what I have so far:

Private Sub CommandButton1_Click()
    Call Loop1
End Sub

Sub Loop1()
    Dim i As Integer
    i = ActiveSheet.Range("F2").Value
    Do
        Call OutlookMailSender
        ActiveSheet.Range("F2").Value = ActiveSheet.Range("F2").Value + 1
    Loop Until (IsEmpty(ActiveCell.Offset(4, 0)))
End Sub

So the file calls Outlook to send a message. But I need it to check before it send the message for a "0" which means the email address is not in our system and to skip this by going to the next customer (adding +1 to customer number) or checking for "1" which means customer email is in system and continue to send the email and loop aftewards.

Upvotes: 1

Views: 493

Answers (2)

thanos.a
thanos.a

Reputation: 2694

It is better to create a dictionary to store CustomerID and the e-mail. Search by the customer id and then try to send the e-mail to that address. It you be much better.

Dim d As Object Set d = CreateObject("Scripting.Dictionary")

d.Add "customer A", "[email protected]"

searchEmail As String

Dim customer As Variant
For Each customer In d.Keys
    If d.Item(customer) = searchEmail Then
        customerID = CStr(customer)

        sendMailTo (customerID)
        exit for
    End If
Next

Upvotes: 0

Dave H
Dave H

Reputation: 653

Here is the syntax for an If/Then/Else in Excel VBA to check the value of a cell, per your specifications.

If (Range("F5").Value = "Not in List") Then
    'code to execute
Else

End If

To access the value (or other properties) of a cell, use Range(). Range() has a lot of other uses, you can look them up here. Range() Excel VBA

Upvotes: 2

Related Questions