0

Can anyone explain why this code returns a Run-time error'91' ?

Dim rng As Range, cell As Range

Set rng = Selection

For Each cell In rng

Next cell

If Not cell.HasFormula Then

End If

cell.Value = WorksheetFunction.Proper(cell.Value)
4
  • Please edit your question to include a brief description of what you are trying to accomplish and what you have attempted to resolve this.
    – CharlieRB
    Dec 6, 2016 at 14:13
  • Your loop does not do anything cell will always be nothing at the end. the Next Cell should be at the end of the code, as it denotes where the loop should end. The End IF should be after the cell.Value = ... It appears you Just threw things together without understanding how they work. Dec 6, 2016 at 14:27
  • You could also do this with one line: Selection.Value = ActiveSheet.Evaluate("INDEX(PROPER(" & Selection.Address & "),)") Dec 6, 2016 at 14:34
  • @Scott Craner a very succinct solution. Thanks
    – Corrado_
    Dec 7, 2016 at 15:34

1 Answer 1

2
For Each cell In rng

Next cell

This steps through each element of the selected range and does... nothing. When the loop is finished, i.e. the code has stepped through each element, the value stored in the object cell is cleared.

If Not cell.HasFormula Then

End If

Now, cell is an empty variable, i.e. it's value is Nothing. Nothing does not have any properties, specifically, it has no HasFormula property

The code I think you want is:

For Each cell In rng
    If Not cell.HasFormula Then
        cell.Value = WorksheetFunction.Proper(cell.Value)
    End If
Next cell

I should add that I've not checked if this code is actually going to run. It's just the "correct" order of execution of the commands you've written.

0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .