My code is not working. Pls look in to it. Thanks for helping.

[pedie]

Hi,

I came across this code sometime back and when i try to run it...it does not work. Looking to run my code in all the sheets except on Home, Sheet3, Sheet2.

Sub working_new()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
  If InStr("Home Sheet3 Sheet2", ws.Name) = 0 Then
    With ws
     On Error Resume Next
     .Range("K1:K" & .Range("J" & .Rows.Count).End(xlUp).Row) _
     .SpecialCells(xlCellTypeBlanks).Value = "x"
     On Error GoTo 0
    End With
 End If
Next ws
End Sub

 

[pedie]

Sorry but i think it is working now.
Please suggest if there is something i did wrong that prevented from not giving results earlier???
I didnt change anything but it worked now....

 

[xld]

You don't say how it should work, but maybe you want

Sub working_new()
Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        If Not IsError(Application.Match(ws.Name, Array("Home", "Sheet3", "Sheet2"), 0)) Then
            With ws
                On Error Resume Next
                .Range("K1:K" & .Range("J" & .Rows.Count).End(xlUp).Row) _
                .SpecialCells(xlCellTypeBlanks).Value = "x"
                On Error GoTo 0
            End With
        End If
    Next ws
End Sub

 

[pedie]

What I'm trying to do is run my code in all the sheets except this 3 sheets.

If J col is not blank then place x in col K. if J col row is blank then end the code.

Hope this is more clear. If more clarification in needed then I am right here.

Thanks again for helping!
It is still not working. though it is not giving any error message.


Pedie


P.S: When i tried running the code u gave me it is even running in sheets ("Home") where i did not want to run...nothing serious..just trying on sampel data

 

[pedie]

Xld are you still around? i think the code is working the other way round.
if sheet name = "home" or "Sheet2" or "Sheet3" then dont run my code else run on all other sheets.

Thanks again

 

[snowblizz]

This alternative approach seems to work.

Sub working_new()
Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        Select Case ws.Name
        Case "Home", "Sheet3", "Sheet2"
        Case Else
            With ws
                On Error Resume Next
                .Range("K1:K" & .Range("J" & .Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeBlanks).Value = "x"
                On Error GoTo 0
            End With
        End Select
    Next ws
End Sub

I experienced a similar problem where it would occasionally not work.

 

[pedie]

Still no results yet for me....
I tried and it did not do anything

I am not sure where I am doing it wrong.

Thanks again!

 

[MrKowz]

Pedie, take out the error handlers and see if it is erroring anywhere for you. If it does error, what error message do you get?

 

[pedie]

When i remove the errror handler it is giving error in yellow region.

Sub working_new1()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
  If InStr("Home Sheet2 Sheet3", ws.Name) = 0 Then
    With ws
     'On Error Resume Next
     [COLOR=yellow].Range("K1:K" & .Range("J" & .Rows.Count).End(xlUp).Row) _
     .SpecialCells(xlCellTypeBlanks).Value = "x"
[/COLOR]     'On Error GoTo 0
    End With
 End If
Next ws
End Sub

the other case code i just recieved did not give any error but doesnt seem to give results....not sure if i am doing something wrong....


Thanks

 

[MrKowz]

Ahh - I see the problem:

Sub working_new1()

[COLOR=black]Dim ws As Worksheet[/COLOR]
[COLOR=black]For Each ws In ActiveWorkbook.Worksheets[/COLOR]
[COLOR=black] If InStr("Home Sheet2 Sheet3", ws.Name) = 0 Then[/COLOR]
[COLOR=black]   With ws[/COLOR]
[COLOR=black]    'On Error Resume Next[/COLOR]
[COLOR=black]    .Range("K1:K" & .Range("J" & Rows.Count).End(xlUp).Row) _[/COLOR]
[COLOR=black]    .SpecialCells(xlCellTypeBlanks).Value = "x"[/COLOR]
[COLOR=black]    'On Error GoTo 0[/COLOR]
[COLOR=black]   End With[/COLOR]
[COLOR=black]End If[/COLOR]
[COLOR=black]Next ws[/COLOR]
[COLOR=black]End Sub[/COLOR]

You had .Rows.Count. That first period should not be there since Rows.Count will return the max # of rows for the worksheet. It is not a sub-method of the Range object.