macro hangs up when I run it on 67 selected ranges

[keithmct]

I received previous help selecting multiple (67) named ranges if they all ended in the word "prices". I wanted to take 10% tax off them all via a simple macro button that multiplied all cells by 0.909. That macro starts but then seems to halt part way through. Thoughts?

 

[Skyybot]

Sounds like you've created a never ending loop. Can you post the Macro?

 

[keithmct]

Private Sub CommandButton1_Click()
'removeGST
For Each cell In Selection
        If cell = "" Then Exit Sub
        cell.Value = cell.Value * 0.909090909

    Next cell
 
End Sub

but when I run the macro over just one or two of the manually selected ranges it works, so .....

 

[bebo021999]

Is there blank cell in selection? If there is, macro will stop at that cell and exit.

 

[Skyybot]

Try this.

Private Sub CommandButton1_Click()
'removeGST
Dim cell as Range
For Each cell In Selection
        If Not IsEmpty(cell) Then 
        cell.Value = cell.Value * 0.909090909

    Next cell
 
End Sub

 

[keithmct]

Is there blank cell in selection? If there is, macro will stop at that cell and exit.

why, yes there is!!. OK I've replaced blank cells with na. Now it errors with run time error 13 type mismatch at "cell.Value = cell.Value * 0.909090909"

 

[Skyybot]

Or this.

Private Sub CommandButton1_Click()
'removeGST
Dim cell as Range
For Each cell In Selection
        If Not cell.Value = "" Then 
        cell.Value = cell.Value * 0.909090909

    Next cell
 
End Sub

 

[keithmct]

should have known I cant multiply na by 0.90909. I'll try putting 0 instead.
edit: I cant do 0 as my markup/profit will be quoted as the full price when I just want it to not give a price at all (or give an error that will make it obvious that item is unavailable)

 

[bebo021999]

or

For Each cell In Selection
        On Error Resume Next
        cell.Value = cell.Value * 0.909090909
        On Error Goto 0
Next cell

 

[keithmct]

or

For Each cell In Selection
        On Error Resume Next
        cell.Value = cell.Value * 0.909090909
        On Error Goto 0
Next cell

that works for both 0 and na - excellent. thanks