ISEMPTY does not want to work properly. Someone help me out of this.

S

Sphinx404

Board Regular
Joined
May 2, 2015
Messages
186
Office Version
  1. 365
Platform
  1. Windows
I am looking to fill in blanks in the selected range with the word "Research"

IF the cell is blank then
cell = "Research"

Simple enough, yet my mind is drawing a blank. The code below puts the word "Research" in the Header (U1), instead of in the blank cell. Something is missing, what in what in the name of Tom Selleck's mustache am I doing wrong. UGH!

Code: 
Dim mycell As Range


For Each mycell In Range("U2", Range("U" & Rows.Count).End(xlUp))


Select Case True
    Case IsEmpty(mycell.Value)
        If IsEmpty(mycell.Value) Then
            mycell.Value = "Research"
        End If
End Select


Next mycell

thanks
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
Maybe this (no loop)

Code: 
With Range("U2:U" & Cells(Rows.Count, "U").End(xlUp).Row)
    .Value = Evaluate("=IF(" & .Address & "="""",""Research""," & .Address & ")")
End With

M.
 
Upvote 0
Is there any data anywhere in column U?
What is the last cell in column U with data?
Are the values currently in column U hard-coded or the result of formulas?
Are there any merged or protected cells in column U?
 
Upvote 0
Joe4 said:
Is there any data anywhere in column U?
What is the last cell in column U with data?
Are the values currently in column U hard-coded or the result of formulas?
Are there any merged or protected cells in column U?
Click to expand...

-Most of the column is full of data.
-All values are created via 2 sub procedures (macro) that I run before I start looking for the blanks (hence this post)
-There are no merged or protected cells.

The Final row is 1957... but this report isn't static, so this procedure needs to run until that last row of data, as it can change with each report I pull.

thanks.
 
Upvote 0
Marcelo Branco said:
Maybe this (no loop)

Code: 
With Range("U2:U" & Cells(Rows.Count, "U").End(xlUp).Row)
    .Value = Evaluate("=IF(" & .Address & "="""",""Research""," & .Address & ")")
End With

M.
Click to expand...


Still populates "Research" in the header "U1".
 
Upvote 0
Please run this test procedure, and let me know what the message box returns:
Code: 
Sub Test()

    Dim lRow As Long
    Dim row1 As Long
    Dim row2 As Long
    
    lRow = Cells(Rows.Count, "U").End(xlUp).Row
    row1 = Len(Cells(1, "U"))
    row2 = Len(Cells(2, "U"))
    
    MsgBox "Rows in column U: " & lRow & vbCrLf & _
            "Length of cell U1: " & row1 & vbCrLf & _
            "Length of cell U2: " & row2
        
End Sub
 
Upvote 0
Sphinx404 said:
Still populates "Research" in the header "U1".
Click to expand...

Don't know how this happened. The range begins in row 2...
Code: 
With Range("U[B][COLOR=#ff0000]2[/COLOR][/B]:U" & Cells(Rows.Count, "U").End(xlUp).Row)
    .Value = Evaluate("=IF(" & .Address & "="""",""Research""," & .Address & ")")
End With

M.
 
Upvote 0
Rows in column U: 1947
Length of cell in U1: 0
Length of cell in U2: 8
 
Upvote 0
Sphinx404 said:
I am looking to fill in blanks in the selected range with the word "Research"

IF the cell is blank then
cell = "Research"

Simple enough, yet my mind is drawing a blank. The code below puts the word "Research" in the Header (U1), instead of in the blank cell. Something is missing, what in what in the name of Tom Selleck's mustache am I doing wrong. UGH!

Code: 
Dim mycell As Range

For Each mycell In Range("U2", Range("U" & Rows.Count).End(xlUp))

Select Case True
    Case IsEmpty(mycell.Value)
        If IsEmpty(mycell.Value) Then
            mycell.Value = "Research"
        End If
End Select

Next mycell
Click to expand...
It sounds like the cells in Column U have formulas in them and the ones you think are blank actually have the equivalent of ="" in them. If I am correct, then the cells you are examining are not blank (empty)... they have a formula in them which means they cannot be blank. Try testing for the length of the contents of the cells equalling zero instead... and get rid of that Select Case for such a simple test...
Code: 
Dim mycell As Range

For Each mycell In Range("U2", Range("U" & Rows.Count).End(xlUp))
  If Len(mycell.Value) = 0 Then
    mycell.Value = "Research"
  End If
Next mycell
 
Upvote 0
How about
Code: 
    Dim UsdRws As Long

    UsdRws = Cells.Find("*", After:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("U2:U" & UsdRws).SpecialCells(xlBlanks).Value = "Research"
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Issues looping through excel data range when blank rows/cells are introduced.
Replies
6
Views
544
Skyybot
Skyybot
G
  • Solved
Insert formula in non-blank cell Loop
Replies
3
Views
540
bebo021999
B
Z
  • Question
Saving workbook as multiple cell values...so close to working. Could someone assist to get me there?
Replies
0
Views
305
ZXsaurus
Z
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
401
AndyEd
A
S
  • Solved
Why does this macro work for my colleague but not me?
Replies
8
Views
3K
Silverjman
S

Forum statistics

Threads
1,215,054
Messages
6,122,895
Members
449,097
Latest member
dbomb1414

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top