Can't use uppercase - type mismatch

[DonAndress]

Hello.

I'd like to use such a simple code but I get Type mismatch error.
In this example "lastFund" returns 32. It doesn't matter if I put 32 instead of "lastFund".
This code passes if I make a reference to one cell only instead of a range.

Range(Cells(14, 4), Cells(lastFund, 4)) = UCase(Range(Cells(14, 4), Cells(lastFund, 4)))

Where do I go wrong?

 

[Jonmo1]

Ucase can only evaluate a single cell at a time.
You'd have to loop through the range, and do them 1 at a time.

For Each c In Range(....)
    c.Value = UCase(c.Value)
Next c

 

[Marcelo Branco]

Maybe...

With Range(Cells(14, 4), Cells(lastFund, 4))
        .Value = Evaluate("=IF(Row(),UPPER(" & .Address & "))")
End With

M.

 

[Rick Rothstein]

Maybe...

With Range(Cells(14, 4), Cells(lastFund, 4))
        .Value = Evaluate("=IF(Row(),UPPER(" & .Address & "))")
End With

Evaluate... "Yes!"... a man after my own heart.

One point... you can save a character by eliminating the equal sign... unlike assigning a formula to the Formula property of a range, the Evaluate function does not need the leading equal sign to know it has received a formula to evaluate.

 

[Marcelo Branco]

Evaluate... "Yes!"... a man after my own heart.

One point... you can save a character by eliminating the equal sign... unlike assigning a formula to the Formula property of a range, the Evaluate function does not need the leading equal sign to know it has received a formula to evaluate.

Yes, now i know how to use Evaluate properly

I've learned in
http://www.mrexcel.com/forum/excel-...ew-columns-via-visual-basic-applications.html

M.

 

[DonAndress]

Good God, Marcelo and Rick.
That's sofisticated for me
I wanted to avoid looping and now I need a minute to understand this code.
But thank you very much!

 

[Marcelo Branco]

Good God, Marcelo and Rick.
That's sofisticated for me
I wanted to avoid looping and now I need a minute to understand this code.
But thank you very much!

You are welcome and thanks for the feedback.

You can find an explanation about how it works at
VBA Trick of the Week :: Avoid Loop for Range Calculations – Evaluate | Useful Gyaan

Hope this helps

M.