Second parameter not working

[rickblunt]

Hello,
I am using the code below to pass some information from a worksheet to a listbox, and it works fine. Well, rather it worked fine when I was just looking for the first value in Column A. But when I added the second parameter ("And sh2.Range("E" & i).Value = "" ") the code no longer returns a value. I just want to return the row information if the value in Column E of the same row is blank.

Did I get the syntax wrong? I appreciate any input.

Private Sub RepairsDueButton_Click()

Dim sh As Object, sh2 As Worksheet
  Dim i As Long
  
  Set sh = Sheets("Plant Status")
  Set sh2 = Sheets("Repair Log")
    
  sh.RepairHistory.Clear
  For i = 1 To sh2.Range("A" & Rows.Count).End(3).Row
    If sh2.Range("A" & i).Value = sh.RepairedDevice.Value And sh2.Range("E" & i).Value = "" Then
      With sh.RepairHistory
        .AddItem
        .List(.ListCount - 1, 0) = sh2.Cells(i, 2).Value
        .List(.ListCount - 1, 1) = sh2.Cells(i, 3).Value
        .List(.ListCount - 1, 2) = sh2.Cells(i, 4).Value
        .List(.ListCount - 1, 3) = sh2.Cells(i, 5).Value
        .List(.ListCount - 1, 4) = sh2.Cells(i, 6).Value
       End With
    End If
  Next

End Sub

 

[Max1616]

It looks like it should be working properly. Are you sure the value in column E is actually blank and not a space or special character? Can you run this above that if Statement to see if it shows as true?

msgbox ("Row " & i & " " & sh2.Range("A" & i).Value = sh.RepairedDevice.Value)
msgbox ("Row " & i & " " & (sh2.Range("E" & i).Value = "")

Are you getting two message boxes that show as true? ( I added in the row number so it's easier to trouble shoot)

 

[rickblunt]

Thanks Max for looking over this for me, I was thinking about trailing spaces and such as well so I went over that pretty thoroughly but could find none; and both message boxes return a false value so I think this confirms it.

Now after reading your insight the one thing that I realize that I hadn't mentioned before is that the cells that the code is getting its values from (Columns A and E) do contain a link to another workbook, so it is not technically blank. When I first started working on the code I was wondering if that would be an issue. But it does not seem to keep the code with the single parameter from working, that one works fine. So I made the assumption that the link wasn't the reason why it wasn't working when I added the second parameter.

The only differences in the two columns are that Column A's returned value is a Text value, while the returned value in E is a date, so its numerical.

At least I know that the syntax is correct, I am always missing a comma or something simple phrase it seems like I really appreciate your input.

(and thank you for the code snippet for the troubleshooting, I am going to add that to my little black book for coding)