CODEWITHSUNDEEP
excelvbaexcel-formula
kit99
kit99

Reputation: 187

Capital letters to cells in column

I'm trying to change all letters into capital letters in all cells holding data in column B2 and down. Got this VBA. 

Sub CapitalLettersColumnB()

    'Capital Letters to names in column B (from B2 and down)
    'Column B holds headers!

    With Range("B2", Cells(Rows.Count, "B").End(xlUp))
        .Value = Evaluate("INDEX(UPPER(" & .Address(External:=True) & "),)")
    End With

End Sub

When my sheet holds 2 rows of data (and more), not counting headers, the vba works just fine. But when my sheet only holds 1 row of data, not counting headers, name in column B (cell B2) is replaced with #VALUE!. Anyone that can help spot what's wrong, and how to correct my vba?

Upvotes: 1

Views: 142

Answers (2)

JvdV
JvdV

Reputation: 75860

The problem is INDEX. The funtion returns a position in a Range or Array, not a single value. If you would evaluate the formula it would come down to this:

=INDEX("VALUE",)

Instead of a correct:

=INDEX({"VALUE1","VALUE2"},)

Not feeding the INDEX a range or array will then return the error

To correct your code you need to make sure there is always a range object larger than one cell, so for example:

Sub CapitalLettersColumnB()

With Range("B1", Cells(Rows.Count, "B").End(xlUp))
    .Value = Evaluate("IF(ROW(" & .Address & ")>1,INDEX(UPPER(" & .Address & "),)," & .Address & ")")
End With

End Sub

This will now work from B1 onwards, but will leave that cell intact through the ROW test in our IF statement.

Another small note, I would definately use an explicit cell reference (including at least a worksheet)

Upvotes: 3

Andreas
Andreas

Reputation: 23958

Honestly not sure what is wrong. (I think it's the same reason as range("B2:B2"). You can't set a range spanning the same cell)
Your Cells(Rows.Count, "B").End(xlUp) returns the value of the last cell which I'm not sure how that works.
But you could change the code to do a with the range to lastrow.
But if the lastrow is 2 the code will fail, so the if goes to else and it only changes the Ucase of cell B2.

If Cells(Rows.Count, "B").End(xlUp).Row > 2 Then
    ' more than one row after B2
    With Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row)
        .Value = Evaluate("INDEX(UPPER(" & .Address(external:=True) & "),)")
    End With
Else
    ' no rows past B2
    Range("B2").Value = UCase(Range("B2").Value)
End If

Upvotes: 1

Related Questions