CODEWITHSUNDEEP
vbaexcel
LBPLC
LBPLC

Reputation: 1571

Executing Code On All Cells, In All Sheets, In Entire Workbook

My issue is that I'm trying to create a code that will change a cell value to an "X" if it contains a value, every cell across multiple sheets needs to be checked (I'm very aware this may take a long time!).

This application is for a huge matrix where the "structure" is more important that the actual values. It's complicated and very difficult to explain (it's created by a 3rd party and very old system and then exported to excel)

I'm sure im along the right lines, but this just doesnt work, I get a "method not supported" fault on line For Each Cell In ws

Sub Usage_X()

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
ws.Activate
    For Each Cell In ws
        If Cell.Value <> "" Then
            Cell.Value = "X"
        End If
    Next
Next


End Sub

Upvotes: 1

Views: 60

Answers (1)

Matt Cremeens
Matt Cremeens

Reputation: 5151

I believe you need to loop through each cell in a range, not in a sheet. So modify to read

Sub Usage_X()

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
Set rng = ws.UsedRange
    For Each Cell In rng
        If Cell.Value <> "" Then
            Cell.Value = "X"
        End If
    Next Cell
Next ws


End Sub

Upvotes: 3

Related Questions