CODEWITHSUNDEEP
excelvbaloopswhile-loop
Andrew
Andrew

Reputation: 47

Problem with a While loop in Excel VBA

I'm trying to write a VBA macro that takes the cell values in a given column, row by row, and assembles an SQL query for me to copypaste. It basically puts together text snippets and variables in a cell. The query needs cardnumbers and ordinal numbers as input, hence the variables.

The macro is almost ready, but it gets stuck in an infinite While loop.

Sub Query()

Dim Row As Integer
Row = 8

Dim Cardnumber As String
Cardnumber = Range("D" & Row)

Dim Number As Integer
Number = 1

Range("E30").Select
ActiveCell.Value = "SELECT cardnumber, first_name || ' ' || last_name FROM ( SELECT cardnumber, first_name, last_name, c.OrderNo FROM ag_cardholder ch, (SELECT '%" & Cardnumber & "%' cardmask, " & Number & " OrderNo from dual "

While IsNull(Cardnumber) = False

    Row = Row + 1
    Number = Number + 1
    Cardnumber = Range("D" & Row)

    ActiveCell.Value = ActiveCell.Value & "UNION ALL SELECT '%" & Cardnumber & "%', " & Number & " OrderNo from dual "

Wend

ActiveCell.Value = ActiveCell.Value & ") c WHERE ch.cardnumber LIKE c.cardmask ORDER BY c.OrderNo ) t"

End Sub

I've tried IsEmpty() instead of IsNull(), the result is the same. Please let me know what I'm missing here. Also, feel free to give me any advice for making the code more elegant as this is my first try at VBA. Thank you in advance for your efforts.

Upvotes: 1

Views: 6188

Answers (1)

GSerg
GSerg

Reputation: 78175

Being a String, Cardnumber will never be Null or Empty. It can only have zero length, Len(Cardnumber) = 0.

If Cardnumber was Variant, you could use IsEmpty to test if a cell value is blank.
There is no point to use IsNull, as a cell value in Excel is never Null. Even if a Null is fetched from a database, Excel will replace it with Empty.

Responding to your next question: I would refactor that code to:

Sub Query()

  Dim InnerSelect As String
  Dim CurCell As Range: Set CurCell = ActiveSheet.Range("D8")
  Dim Number As Long: Number = 1

  Do
    Dim Cardnumber As String
    Cardnumber = CurCell.Value

    If Len(Cardnumber) = 0 Then Exit Do

    If Len(InnerSelect) = 0 Then
      InnerSelect = "SELECT '%" & Cardnumber & "%' cardmask, " & Number & " OrderNo from dual "
    Else
      InnerSelect = InnerSelect & "UNION ALL SELECT '%" & Cardnumber & "%', " & Number & " OrderNo from dual "
    End If

    Number = Number + 1
    Set CurCell = CurCell.Offset(1, 0)
  Loop

  Range("E30").Value = _
    "SELECT cardnumber, first_name || ' ' || last_name FROM ( SELECT cardnumber, first_name, last_name, c.OrderNo FROM ag_cardholder ch, (" & _
    InnerSelect & _
    ") c WHERE ch.cardnumber LIKE c.cardmask ORDER BY c.OrderNo ) t"

End Sub

Upvotes: 3

Related Questions