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

[Sphinx404]

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!

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

 

[Marcelo Branco]

Maybe this (no loop)

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

M.

 

[Joe4]

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?

 

[Sphinx404]

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?

-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.

 

[Sphinx404]

Maybe this (no loop)

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

M.

Still populates "Research" in the header "U1".

 

[Joe4]

Please run this test procedure, and let me know what the message box returns:

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

 

[Marcelo Branco]

Still populates "Research" in the header "U1".

Don't know how this happened. The range begins in row 2...

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.

 

[Sphinx404]

Rows in column U: 1947
Length of cell in U1: 0
Length of cell in U2: 8

 

[Rick Rothstein]

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!

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

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...

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

 

[Fluff]

How about

    Dim UsdRws As Long

    UsdRws = Cells.Find("*", After:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("U2:U" & UsdRws).SpecialCells(xlBlanks).Value = "Research"