VBA: looping through worksheets using nested For Each having worksheet as variable

Question

Newbie at vba here. I'm trying to apply a simple For Each loop (which nullifies cells < 0) to all worksheets in the workbook by nesting this inside another For Each loop.

When I try and run my code below I get an error and I'm not sure if it has anything to do with having worksheet as a variable within a Set statement.

Can't seem to figure this out/find a solution.

Thanks

Sub deleteNegativeValue()

Application.DisplayAlerts = False
Dim lastRow As Long
Dim ws As Worksheet
Dim cell As Range
Dim res As Range

For Each ws In Workbooks(1).Worksheets

Set res = ws.Range("1:1").Find("Value", lookat:=xlPart)
lastRow = ws.Range("A" & Rows.Count).End(xlUp).Row

For Each cell In Range(ws.Cells(1, res.Column), ws.Cells(lastRow, res.Column))
If cell < 0 Then cell = ""
Next

Next

End Sub

Run-time error '91': Object variable or With block variable not set. Error seems to be on the second For Each bit — Kactus88
– Kactus88, Commented Apr 5, 2016 at 14:15
Besides the suggestion below, you will want to put in a test to make sure that "Value" is found, you are probably returning a range object with nothing in it. — Scott Craner
– Scott Craner, Commented Apr 5, 2016 at 14:27
Instead of Dim cell As Range I think it should be Dim cell As Cell — Paul Ogilvie
– Paul Ogilvie, Commented Apr 5, 2016 at 14:29

Scott Craner · Accepted Answer · 2016-04-05 14:35:24Z

1

Try this:

Sub deleteNegativeValue()

Dim lastRow As Long
Dim ws As Worksheet
Dim cell As Range
Dim res As Range

For Each ws In ThisWorkbook.Worksheets

    Set res = ws.Range("1:1").Find("Value", lookat:=xlPart)
    lastRow = ws.Range("A" & Rows.Count).End(xlUp).row
    If Not res Is Nothing Then
        For Each cell In ws.Range(ws.Cells(1, res.Column), ws.Cells(lastRow, res.Column))
            If cell < 0 Then cell = ""
        Next
    Else
        MsgBox "No Value found on Sheet " & ws.Name
    End If

Next

End Sub

There needs to be a check on the Find method, to ensure that something was found

answered Apr 5, 2016 at 14:35

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kactus88 Over a year ago

Thank you so much for this:)

Kactus88 Over a year ago

The check on the Find was definitely needed.

user3598756 · Accepted Answer · 2016-04-05 15:22:11Z

you could try this

Option Explicit

Sub deleteNegativeValue()

Dim ws As Worksheet
Dim res As Range

For Each ws In ThisWorkbook.Worksheets
    Set res = Intersect(ws.Rows(1), ws.UsedRange).Find("value", LookAt:=xlPart)
    If Not res Is Nothing Then
        ws.Columns(res.Column).SpecialCells(xlCellTypeConstants, xlNumbers).Replace What:="-*", Replacement:="", SearchOrder:=xlByColumns, MatchCase:=False, LookAt:=xlWhole
    Else
        MsgBox "No Value found on Sheet " & ws.Name
    End If
Next

End Sub

which should run faster since it doesn't iterate through every cell of each column and restrict the Find method range to the used one instead of the entire row.

the only warning is that the first row of all searched in sheets must not be empty...

Vityata · Accepted Answer · 2016-04-05 14:18:38Z

0

Try the second for-each this way:

ws.Range(ws.Cells(1, res.Column), ws.Cells(lastRow, res.Column))

answered Apr 5, 2016 at 14:18

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Collectives™ on Stack Overflow

VBA: looping through worksheets using nested For Each having worksheet as variable

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related