Repeating Macro through Cells and Worksheets

[Strup]

Hi all,

I am having some issues with repeating the macro using the 'next' function. What I want to do is change any cell that has a '0' in it to '1E-10'

I currently have this so far for one sheet but am having some difficulty exiting the loop and going to the next worksheet -

Sub Replace0()

Dim ActiveCell As Variant

For Each ActiveCell In Range("A1:AI1000")

If ActiveCell.Value = "0" Then

ActiveCell.Value = "0.0000000001"

End If

Next

End Sub

Any help would be much appreciated.

[6StringJazzer]

Welcome to the Forum Strup!

First, it is your first post so I will remind you of the rules (see below) but also answer your question. Please ensure you review the rules and observe them in future posts.

I strongly recommend against using ActiveCell as a variable name because it is a built-in object in Excel VBA and you could really get tangled up with that.

Do you really need to review every cell in the range A1:AI1000? An alternative might be to use CurrentSheet.UsedRange if you don't know in advance which cells are being used.

Please Login or Register  to view this content.

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

[Strup]

Hello,

Thanks for your reply and sorry for the layout.

Whilst this works on a test spreadsheet, as soon as I use this module in my required worksheet (which has a variety of columns, text, etc), I recieve the following message - Run-time error '13': Type mismatch

For -

Please Login or Register  to view this content.

Any ideas why?

Thanks again!

[AB33]

Try one of these

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[JOHN H. DAVIS]

Another:

Please Login or Register  to view this content.