Unsure Why I Am Getting Error: First VBA Experience

[caasi6636]

Earlier I was trying to code a program to count the rows and columns of all the individual sheets of my work book, and then save all of the results into an Array. I have it connected to a button, and sometimes it will just give me 0s as a result when I print the array, and others times I will get an error.

I will paste my code below in hopes that one of you may be able to aid me. I understand it is very sloppy, and I have looked up a more succinct way of completing what I aim for. I am simply wondering where I messed up in my original thought process.

Private Sub CommandButton1_Click()


Dim Dimensions(1 To 50, 1 To 50) As Integer


Dim WS_Count As Integer


Dim Ix As Integer


Dim Jx As Integer


Dim X As Integer
        
Dim Rows As Integer


Dim Cols As Integer


    Ix = InputBox("Please Enter The Starting # of OFFSET For your Rows (Ex If You have Labelled Columns Or Rows, Number from the top)")
    Jx = InputBox("Please Enter The Starting # of OFFSET For your Columns (Rare, but if you had labels on the left side)")
    
    WS_Count = ActiveWorkbook.Worksheets.Count
    
        For X = 1 To (WS_Count - 1)
        
            i = Ix
            j = Jx
            
            Do While ActiveWorkbook.Worksheets(X).Cells(i, 2).Value = "*"
        
                Rows = Rows + 1
                i = i + 1
            
            MsgBox "i:" & i
            
            Loop
        
            Do While ActiveWorkbook.Worksheets(X).Cells(3, j).Value = "*"
        
                Cols = Cols + 1
                j = j + 1
            
            MsgBox "j:" & j
            
            Loop
            
            Dimensions(X, 1) = Rows
            Dimensions(X, 2) = Cols
            
        
        
        
        Next X
        
    Dim k As Integer, l As Integer


For k = 1 To 5
    For l = 1 To 2
        Cells(k, l).Value = Dimensions(k, l)
    Next l
Next k


End Sub

 

[tonyyy]

caasi6636,

Welcome to the Board.

Do While ActiveWorkbook.Worksheets(X).Cells(i, 2).Value = "*"

When the code runs, the above line produces a "0" result. When testing for a non-blank cell, you might consider...

Do While ActiveWorkbook.Worksheets(X).Cells(i, 2).Value <> ""

If the user enters a zero (0) in the InputBox for Ix, the code will error - as .Cells(i,2) will refer to Row 0, which does not exist; the same error will occur if a zero is entered for Jx.

...I have looked up a more succinct way of completing what I aim for.

Hopefully you found what you were looking for; another approach could be the following...

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim wsCount As Long, i As Long
Dim Ix As Long, Jx As Long
Dim Roze As Long, Columnz As Long
Dim dimensions As Variant

Ix = Application.InputBox(prompt:="Please Enter The Starting # of OFFSET For your Rows (Ex If You have Labelled Columns Or Rows, Number from the top)", Type:=1)
Jx = Application.InputBox(prompt:="Please Enter The Starting # of OFFSET For your Columns (Rare, but if you had labels on the left side)", Type:=1)

wsCount = Sheets.Count
ReDim dimensions(1 To wsCount, 1 To 3)
For i = 1 To wsCount
    Roze = -Ix
    Columnz = -Jx
    Roze = Roze + Sheets(i).Cells(Rows.Count, 2).End(xlUp).Row
    Columnz = Columnz + Sheets(i).Cells(2, Columns.Count).End(xlToLeft).Column
    dimensions(i, 1) = Sheets(i).Name
    dimensions(i, 2) = Roze
    dimensions(i, 3) = Columnz
Next i

Sheets.Add after:=Sheets(Sheets.Count)
Range("A1:C" & wsCount).Value = dimensions
End Sub

Cheers,

tonyyy