Function looks at Sheet1 instead of active

[Reset]

You are going to say "please use code tags". I don't know how, please show me. And on to the question!

I am trying to run through several sheets and pull up data from them. However, even though it shows (via msgBox to pause execution) I am on the sheet of choice, all the data calls are going to Sheet1. Here's the code:

Private Sub CommandButton1_Click()
c = Sheets.Count
For Z = 1 To c
If Sheets(Z).Name = "Sheet1" Then GoTo line1
If Sheets(Z).Name = "dat" Then GoTo line1
Sheets(Z).Activate
Sheets(Z).Select
    a = Application.WorksheetFunction.CountA(Columns(1))
    MsgBox a
    b = Application.WorksheetFunction.CountA(Columns(9))
    
    If a - b < 1 Then GoTo line1
    ReDim q(a - b) As Integer
    inc = 0
    For x = 2 To a
        If Cells(x, 9) = "" Then q(inc) = Cells(x, 1): inc = inc + 1
    Next x
    Sheets("dat").Select
        v = Application.WorksheetFunction.CountA(Columns(1))
        For y = 1 To inc
            Cells(v + y, 1) = q(y - 1)
            Cells(v + y, 2) = Sheets(Z).Name
            Cells(v + y, 3) = Cells(v + y, 2) & ", " & Cells(v + y, 1)
        Next y
        
line1: 'jump out
Next Z

The MsgBox a gives 0, which is the expected result in Sheet1 (I put garbage in column 1 to verify and it comes out with expected result). So why is this going to Sheet1?

 

[RoryA]

Code tags:
[code]code goes here[/code]

 

[GlennUK]

Shouldn't that be:

a = Application.WorksheetFunction.CountA(Sheets(Z).Columns(1))

etc etc?

 

[repairman615]

Hello,

What is the REDIM statement doing? Should q be equal to a-b?

While creating a post in the editing window that we type into, you will see a pound symbol (#), this will insert the code tags around highlighted text or insert the tags with cursor in between.

Private Sub CommandButton1_Click()
c = Sheets.Count
    For Z = 1 To c
        If Sheets(Z).Name = "Sheet1" Or Sheets(Z).Name = "dat" Then GoTo line1
            Sheets(Z).Activate
            a = Application.WorksheetFunction.CountA(Columns(1))
                MsgBox a
            b = Application.WorksheetFunction.CountA(Columns(9))
        If a - b < 1 Then GoTo line1
 
        'ReDim q(a - b) As Integer
        q = a - b
 
            inc = 0
        For x = 2 To a
            If Cells(x, 9) = "" Then q(inc) = Cells(x, 1): inc = inc + 1
        Next x
 
        Sheets("dat").Select
            v = Application.WorksheetFunction.CountA(Columns(1))
                For y = 1 To inc
                    Cells(v + y, 1) = q(y - 1)
                    Cells(v + y, 2) = Sheets(Z).Name
                    Cells(v + y, 3) = Cells(v + y, 2) & ", " & Cells(v + y, 1)
                Next y
line1: 'jump out
    Next Z
End Sub

 

[Reset]

Glenn,

Yes that's true, but I am wondering why. I have done it before without having to call out the sheet name every time. Is it perhpas because it is coming from "Private Sub CommandButton1_Click()"? I've actually left out a bit and will be putting in more code and it get tedious. Thanks.

 

[RoryA]

If you refer to Range in code located in a worksheet code module, it refers to a range on that same sheet, not the active sheet. You must specify (as you always should if possible) which sheet the range belongs to.

 

[Reset]

Still going nuts here. I put in sheet references everywhere, but there is still one issue. I need to clear all the contents of the sheet named "dat" before repopulating it with this data. At the very beginning I've tried everything but can't get it to work. I have tried:

Sheets("dat").Select
Range(Columns(1), Columns(3)) = ""
' clears range on Sheet1
 
Sheets("dat").Select
Columns("A:C").Select
Selection.ClearContents
' this is from the record macro function and when placed in my code give
'run-time error 1004: application-defined or object-defined error
 
Sheets("dat").Range(Columns(1), Columns(3)).Delete
' run-time error 1004 again

And a couple of other variants. Why can't this work?

 

[RoryA]

You're not qualifying the range or columns statements. You can just use:

Sheets("dat").Range("A:C").ClearContents