LinkBack URL
About LinkBacksI built a loop which I knew was going to have various
objects and collections. So I decided to set up a
variable called ObjCollArray which I declared as a Public
Variable. Then when I enter the For Next Loop, I use the
format:
For Each ObjCur in ObjCollArray(variable)
Next
Problem is that this loop is within another For Each Loop
which loops through my sheets. When I do a Debug Print on
the sheet name and the ObjCollArray.Parent.Name, they do
not equal each other.
Debug.Print sh.Name, ObjCollArray(AuditTypes).Parent.Name
Secondly, as my sheets loop, ObjArray.Parent.Name stays on
the same sheet. It never updates.
I can't figure out why. I am wondering if its is because
I have set up my array saying ActiveSheet. Here is my code
Public ObjCollArray As Variant
Public Comrng As Range
Public Hardrng As Range
Public Errrng As Range
Public Colrnge As Range
Public Validrng As Range
Public ValidErrrng As Range
Public ObjType As String
Public CollType As String
Public CurObj As Object
Private Sub OKButton_Click()
Set ObjCollArray(0) = ActiveSheet.Comments
Set ObjCollArray(1) = ActiveSheet.UsedRange
Set ObjCollArray(2) = ActiveSheet.UsedRange
Set ObjCollArray(3) = ActiveSheet.UsedRange
Set ObjCollArray(4) = ActiveSheet.UsedRange
Set ObjCollArray(5) = ActiveSheet.UsedRange
Private Sub ListAuditResults()
Dim PasteStartCell As String
Dim sh As Worksheet
Dim sh1 As Worksheet
Dim AuditTypes As Integer
Dim AuditShtName As String
Application.Calculation = xlManual
'Application.ScreenUpdating = False
On Error Resume Next
'Set up name of new summary sheet
Set sh1 = ActiveWorkbook.Sheets("Audit Results")
On Error GoTo 0
'If Sheet called "Audit Results" already exists
'then delete it and prepare to create a new one
If Not sh1 Is Nothing Then
Application.DisplayAlerts = False
sh1.Delete
Application.DisplayAlerts = True
End If
With ActiveWorkbook
'Add a worksheet for results to be pasted to
.Worksheets.Add(After:=.Worksheets
(.Worksheets.Count)).Name = "Audit Results"
'Set up column headings for summary report
'these will set up based on the numbers
'options chosen.
PasteStartCell = Range("B2").Address
'Set first paste cell and column header for Commented
Cells
If ComChkBx = True Then
Set Comrng = .Worksheets("Audit Results").Range
(PasteStartCell).Offset(0, ChkbxArray(0, 1) * 2 - 2)
Comrng.Offset(-1, 0) = "Cell Comments"
End If
'Set first paste cell and column header for Hard Coded
Cells
If HardCodedChkBx = True Then
Set Hardrng = .Worksheets("Audit Results").Range
(PasteStartCell).Offset(0, ChkbxArray(1, 1) * 2 - 2)
Hardrng.Offset(-1, 0) = "Hard Coded Cells"
End If
'Set first paste cell and column header for Cells with
Errors
If ErrorChkBx = True Then
Set Errrng = .Worksheets("Audit Results").Range
(PasteStartCell).Offset(0, ChkbxArray(2, 1) * 2 - 2)
Errrng.Offset(-1, 0) = "Errors"
End If
'Set first past cell for data validation cells
If DataValChkBx = True Then
Set Validrng = .Worksheets("Audit Results").Range
(PasteStartCell).Offset(0, ChkbxArray(3, 1) * 2 - 2)
Validrng.Offset(-1, 0) = "Validation"
End If
'Set first past cell for data validation cells
If DataValErrChkBx = True Then
Set ValidErrrng = .Worksheets("Audit
Results").Range(PasteStartCell).Offset(0, ChkbxArray(4, 1)
* 2 - 2)
ValidErrrng.Offset(-1, 0) = "Validation Errors"
End If
'This should equal the entire number of Audit Types
'in the userfor. It should be the total amount
'not only the ones that were chosen
'Note these numbers feed are compared to a Select
'Case stmt in the main module. So they cannot start
'with 0 (i.e. 0 to 5 must be 1 to 6)
For Each sh In .Worksheets
If LCase(sh.Name) <> "Audit Results" Then
'After its been determined that the sheet is not the
'comments sheet, code checks various conditions
'For AuditTypes = 0 To 5
For Each CurObj In ObjCollArray(1)
'Debug.Print CurObj.Parent.Name, CurObj.Address
Debug.Print sh.Name, ObjCollArray
(AuditTypes).Parent.Name
ObjType = TypeName(CurObj)
CollType = TypeName(ObjCollArray(1))
Call MainAudit(2)
Next
'Next
End If
Next
End With
Register To Reply
Bookmarks