breynolds0431
Active Member
- Joined
- Feb 15, 2013
- Messages
- 303
- Office Version
- 365
- 2016
- Platform
- Windows
I've fallen into a big hole with this issue. I have a userform that compiles all the columns on a sheet in a listbox. The user goes through selecting the column and indicating the data type (date/time, text, or numeric). That information is written to a separate sheet, which will be used externally to verify all data types were properly matched/identified. The userform works fine. My goal was to use the defined data types and run some loops through the data sheet to ensure each column's cells is what was indicated in the userform (e.g.; column of payment dates should not have any text). If an issue like that is found, it's written to another sheet that compiles all the errors. Each error will be written to the sheet with the sheet's name, corresponding cell address, and error type.
What I can't figure out is how to loop through the variable column (column headers will very drastically between different sheets) after determining it's data type and checking each cell within. Here is what I have so far. But, I receive the "For Each can only iterate over a collection object or an array" error on the
What I can't figure out is how to loop through the variable column (column headers will very drastically between different sheets) after determining it's data type and checking each cell within. Here is what I have so far. But, I receive the "For Each can only iterate over a collection object or an array" error on the
cell.column part, which was my longshot approach for determining the column of the current column header's data type. And I'm sure there are other errors waiting for the cell.column issue to be resolved. I greatly appreciate any time that is spent looking through this.
VBA Code:
Private Sub cbVerify_Click()
Dim sSheet As String: sSheet = "IPXO" 'Source sheet; needs to be changed to a variable, but using static for testing
Dim lRow As Long: lRow = Sheets(sSheet).UsedRange.Rows(Sheets(sSheet).UsedRange.Rows.Count).Row
Dim lCol As Long: lCol = Sheets(sSheet).UsedRange.Columns(Sheets(sSheet).UsedRange.Columns.Count).Column
Dim cell As Range
For Each cell In Sheets(sSheet).Rows(1) 'Looks through the first row (which are the column headers)
Dim dVerify As Worksheet: Set dVerify = ThisWorkbook.Sheets("ColData") 'Sheet that has the column headers and their defined data types
Dim shCol As Variant: shCol = Application.WorksheetFunction.Match(sSheet, dVerify.Rows(1), 0)
Dim var As Variant
Dim dv As Worksheet: Set dv = ThisWorkbook.Sheets("Data Verify") 'Sheet that any errors found in sSheet will be written to
Dim lrowDV As Long
Dim rng As Range: rng = Sheets(sSheet).CurrentRegion
'converts any formulas to values
Dim fCell As Range
For Each fCell In rng
If fCell.HasFormula Then
fCell.Formula = fCell.Value
End If
Next fCell
' Verifies no blanks
Dim bCell As Range
For Each bCell In rng
If IsEmpty(bCell.Value) Or bCell.Value = vbNullString Then
With dv
lrowDV = dv.Cells(dv.Rows.Count, "C").End(xlUp).Row
With dv
.Activate
.Cells(lrowDV + 1, 2).Value = sSheet
.Cells(lrowDV + 1, 3).Value = bCell.Address
.Cells(lrowDV + 1, 4).Value = "Blank Cell"
End With
End With
End If
Next bCell
var = Application.WorksheetFunction.VLookup(cell, dVerify.Range("A:AA"), shCol, False)
If var = "Date/Time" Then
' Will ensure Date Format
Dim dCell As Range
For Each dCell In cell.Column
If Not IsDate(dCell.Value) Then
If dCell.Row = 1 Then
Else
With dv
lrowDV = dv.Cells(dv.Rows.Count, "C").End(xlUp).Row
With dv
.Activate
.Cells(lrowDV + 1, 2).Value = sSheet
.Cells(lrowDV + 1, 3).Value = dCell.Address
.Cells(lrowDV + 1, 4).Value = "Date Format Error"
End With
End With
End If
End If
Next dCell
ElseIf var = "Text" Then
' Will review data indicated as Text to not be in number format
Dim tCell As Range
For Each tCell In cell.Column
If IsNumeric(tCell.Value) Then
If tCell.Row = 1 Then
Else
With dv
lrowDV = dv.Cells(dv.Rows.Count, "C").End(xlUp).Row
With dv
.Activate
.Cells(lrowDV + 1, 2).Value = sSheet
.Cells(lrowDV + 1, 3).Value = tCell.Address
.Cells(lrowDV + 1, 4).Value = "Text Format Error"
End With
End With
End If
End If
Next tCell
ElseIf var = "Numeric" Then
' Will ensure Number Format
Dim nCell As Range
For Each nCell In cell.Column
If Not IsNumeric(nCell.Value) Then
If nCell.Row = 1 Then
Else
With dv
lrowDV = dv.Cells(dv.Rows.Count, "C").End(xlUp).Row
With dv
.Activate
.Cells(lrowDV + 1, 2).Value = sSheet
.Cells(lrowDV + 1, 3).Value = nCell.Address
.Cells(lrowDV + 1, 4).Value = "Numeric Format Error"
End With
End With
End If
End If
Next nCell
Else
End If
Next cell
End Sub
