VBA IsEmpty Function not working properly

[cg1576]

Hi,
I have a database of our clients, and the columns are arranged in the following way
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]ID[/TD]
[TD]NAME[/TD]
[TD]PHONE[/TD]
[TD]ADDRESS[/TD]
[TD]CITY[/TD]
[TD]STATE[/TD]
[TD]ZIP[/TD]
[TD]WEBSITE[/TD]
[TD]E-MAIL/CONTACT FORMS[/TD]
[TD]INDUSTRY[/TD]
[TD]STATUS[/TD]
[/TR]
</tbody>[/TABLE]



I have the following macro, which works great. It basically loops through the database and pulls up the Name (B) and e-mail/contact form (I) of those that meet the two criteria (which are *http* in I and "Sent" in K).

Dim LastRow As Long
   Dim i As Long, j As Long


   'Find the last used row in a Column: column A in this example
   With Worksheets("Tracker")
      LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
   End With


   MsgBox (LastRow)
   'first row number where you need to paste values in Sheet1'
   With Worksheets("Contact Form To Send")
      j = .Cells(.Rows.Count, "A").End(xlUp).Row
   End With


   For i = 2 To LastRow
       With Worksheets("Tracker")
           If InStr(.Cells(i, 9).Value, "http") And .Cells(i, 11).Value = "Sent" Then
               .Cells(i, 2).Copy Destination:=Worksheets("Contact Form To Send").Range("A" & j)
               .Cells(i, 9).Copy Destination:=Worksheets("Contact Form To Send").Range("B" & j)
               j = j + 1
           End If
       End With
   Next i

Now I have four different status options and some of them do not have a status (empty cell in K). I want to pull up those that do not have a status, but the IsEmpty function doesn't seem to work, it just pulls ALL the rows that meet the first criteria, regardless of the K column.

Dim LastRow As Long
   Dim i As Long, j As Long


   'Find the last used row in a Column: column A in this example
   With Worksheets("Tracker")
      LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
   End With


   MsgBox (LastRow)
   'first row number where you need to paste values in Sheet1'
   With Worksheets("Contact Form To Send")
      j = .Cells(.Rows.Count, "A").End(xlUp).Row
   End With


   For i = 2 To LastRow
       With Worksheets("Tracker")
           If InStr(.Cells(i, 9).Value, "http") And IsEmpty(Cells(i, 11)) Then
               .Cells(i, 2).Copy Destination:=Worksheets("Contact Form To Send").Range("A" & j)
               .Cells(i, 9).Copy Destination:=Worksheets("Contact Form To Send").Range("B" & j)
               j = j + 1
           End If
       End With
   Next i

I am new to VBA, I have tried different iterations based on different things I saw online such as: IsEmpty(Range("K" & i).Value) and even IsEmpty(Cells(i, 11)) = True. I don't get any errors, the macro runs its course, it just doesn't seem to take the IsEmpty function into consideration.


Any help would be very appreciated.

CG

 

[Fluff]

You've forgotten the . before Cells

IsEmpty(.Cells(i, 11))

 

[cg1576]

I can't believe I missed that. I was probably over complicating it.

Thank you so much.

 

[Fluff]

You're welcome