Difference between 3 codes to see if cell is empty

[pincivma]

Hi There

This is a very easy question to answer. I really get confused about the following 3 lines of code. I have used all three but I still do not know what the difference is. Here they are:

1. If ActiveCell = "" Then
Code goes here
Loop Until ActiveCell = ""

2. If IsBlank(ActiveCell) Then
Code goes here
Loop Until IsBlank(ActiveCell

3. If ActiveCell = Blank Then
Code goes here
Loop Until activeCell = Blank

1. I believe that the first code stops at a cell that is actually blank or appears blank but might have a formula in it. Is this correct?
2. The second code stops at a cell that is actually blank but does not stop at a cell that appears blank but has a formula in it. Is this correct?
3 I'm not sure about the third line of code.

Can someone out there tell me if I'm right on the first 2 codes and if not explain them better to me. Also tell me what the third code does. Is it the same as the first code but written differently?

 

[pincivma]

Sorry, I made a mistake in numbers 2. This is the correct macro

2. If ActiveCell = "" Then
Code goes here
Loop Until IsEmpty(ActiveCell)

This code when tested goes into an ever ending loop. Why is that??

 

[Yongle]

Below is some code to help you - experiment with different values and other functions

What the code does
A string is added to whenever correct type of cell is found and returned via message box at the end
Note that VBA functions are not dealt with in the same way as Excel functions
(to confuse you further, some Excel function names are the same as their VBA equivalents but not always!)
Included are 2 ways to use worksheet functions

Sample values
Put these values/formulas in cells A1:A8 and then run the code

A1 - edit cell and insert any number
A2 - edit cell and insert any text
A3 - This formula =A1&A2
A4 -This formula =IF(A3="1a","'","")
A5 - This formula =IF(A4="1a","'","")
A6 - edit cell and insert apostrophe only
A7 - edit cell and insert space only
A8 - edit cell and {Alt} {Enter} which creates a line break

VBA

Sub WhatsInThatCell()
Dim cel As Range, s As String
    Set cel = Range("A1")
    Do Until IsEmpty(cel)
        Set cel = cel.Offset(1)
    Loop
    s = s & vbCr & cel.Address(0, 0) & vbTab & "VBA" & vbTab & "IsEmpty"
    Set cel = Range("A1")
    Do Until Evaluate("ISBLANK(" & cel.Address & ")")
        Set cel = cel.Offset(1)
    Loop
    s = s & vbCr & cel.Address(0, 0) & vbTab & "Excel" & vbTab & "IsBlank" 
    Set cel = Range("A1")
    Do Until cel = ""
        Set cel = cel.Offset(1)
    Loop
    s = s & vbCr & cel.Address(0, 0) & vbTab & "VBA" & vbTab & "Empty Quotes"
    Set cel = Range("A1")
    Do Until cel = vbNullString
        Set cel = cel.Offset(1)
    Loop
    s = s & vbCr & cel.Address(0, 0) & vbTab & "VBA" & vbTab & "vbnullstring"
    Set cel = Range("A1")
    Do Until Evaluate("ISNUMBER(" & cel.Address & ")")
        Set cel = cel.Offset(1)
    Loop
    s = s & vbCr & cel.Address(0, 0) & vbTab & "Excel" & vbTab & "IsNumber"
    Set cel = Range("A1")
    Do Until Evaluate("ISTEXT(" & cel.Address & ")")
        Set cel = cel.Offset(1)
    Loop
    s = s & vbCr & cel.Address(0, 0) & vbTab & "Excel" & vbTab & "IsText" & vbTab
    Set cel = Range("A1")
    Do Until WorksheetFunction.IsFormula(cel)
        Set cel = cel.Offset(1)
    Loop
    s = s & vbCr & cel.Address(0, 0) & vbTab & "Excel" & vbTab & "IsFormula"

MsgBox s, , "First Found"
End Sub

Generally avoid the risk of an endless loop with something like
If cel.Row > 10000 Then 
  Exit Do
End If

But remember to consider what needs to happen next
-eg in my code the message string would be incorrect etc

 

[pincivma]

Hi Yongle
Thanks. I will try the code and see what it does.

 

[JackDanIce]

For your own knowledge, it's generally faster when looking over a range in a sheet to use FOR NEXT with index numbers, than objects.

E.g. If you're looping over column A, know the first cell starts in row 2, but do not know the last cell row number, you can use:

Dim x as Long
Dim LastRow as Long

LastRow = Range("A" & Rows.Count).End(xlup).Row
'Or LastRow = Cells(Rows.count, 1).End(xlUp).Row
'Range is more intuitive to worksheet Excel, but I'm used to cells and using numbers for rows and columns, rather than strings/letters

For x = 2 to LastRow
  Cells(x, 1).Value = x
  'Or Range("A" & x).Value = x
  
  ' If you need to exit the loop or if your range isn't continuous between start and end points (i.e. blank cells exist)
  If Cells(x,1).Value = "" Then Exit For
Next x

I can't recall when I last used DO LOOP or LOOP UNTIL, IMO nearly all loops over data in a sheet can be achieved using FOR NEXT

You can also test for empty cells with

=Len(Range("A" & x).Value) > 0

 

[pincivma]

HI JackDanIce

Thank you for your code. I'm used to using Loop until ActiveCell = "". The reason is that many times in a column, I need the cursor to stop at many empty rows and then add code to do many routines. I will try and use FOR NEXT.