MS Excel 2010 - VBA to lookup in one column a customer number and Tag the corresponding column with Yes or No

Question

I have an extremely large dataset with customer numbers and we cannot just use a =IF(E3=160248, "YES", "NO") to tag a particular customer number of 160248 with YES or NO. Instead, I would like to use VBA code to lookup Customer_Number in column E and return a YES or NO in the corresponding row in Column AG, called Incorporated_160248. I have not done an If then clause in VBA, so I have no idea where to start. Please note, each month the data set can change. One month it could be 4,000 entries and the next 3,500, so that has to be dynamic. Any thoughts?

Sub TagTryco()


Dim CN As Integer, result As String
CN = Range("E:E").Value

  If CN = 160248 Then
    result = "YES"
  Else
    result = "NO"
  End If

  Range("AG:AG").Value = result

  End Sub

I get a Compile error: Wrong number of arguments or invalid property assignment.

This CODE Works now:

Sub TagTryco()

Dim listLength
listLength = Worksheets("ILS_Import").Cells(Rows.Count, "E").End(xlUp).Row - 1

Dim i As Integer
For i = 2 To listLength + 2
   If Worksheets("ILS_Import").Range("E" & i) = 160248 Then
     Worksheets("ILS_Import").Range("AG" & i) = "Yes"
   Else
     Worksheets("ILS_Import").Range("AG" & i) = "No"
   End If
 Next

 End Sub

Answer 1

To know how many entries you have:

dim listLength
listlength = Sheet1.Cells(Rows.Count, "E").End(xlUp).Row - 1 'I assumed column E, starting at row 2

You need to loop from row 2 to the row 2 + listLength, check the cell in column E, and check if it is equal to your number:

dim i as integer
for i = 2 to listLength +  2
    If Range("E" & i) = 160248 Then 
         Range("AG" & i) = "Yes"
    Else
         Range("AG" & i) = "No"
    End If
Next

If you wish to scan for different numbers you can adapt the code to use a value from a cell in which you enter that number, OR use an inputbox to enter the number you want to look for, or something else. This code was not tested.

If you want to use the column name you assigned instead of AG (which is safer) you can use something along the lines of:

 = Range("Incorporated_160248")(i+1)

Instead, which gives the column with an offset of i. Should bring you to the right cell.