Check for column sizes

[Lhuffst]

What is the best way to check and see if a column has the correct number of digits (text or number).

I wrote a function that checks the length and returns an error message as the last step on an import function. That works for the first column but not the remaining columns. CmdUpdateImport is run first

[b]
Private Sub CmdUpdateImport_Click()[/b]
Dim fopen As Variant, strFile As String, lrow As Long

 lrow = Sheet1.UsedRange.Rows.Count  'last row used
TtlCol = Application.Columns.Count  'last column used

'just make sure the button size doesn't change
Sheet1.Range(Cells(1, "A"), Cells(1, "K")).RowHeight = 27.75
Sizeit

'clear the data only -- leave the headers
Sheet1.Range(Cells(3, "a"), Cells(lrow, TtlCol)).ClearContents
fopen = Application.GetOpenFilename("ExcelSheets (*.xls),*.xls")
If fopen = False Then
  Exit Sub
End If
strFile = Replace(Dir(fopen), ".xls", "", , , vbTextCompare)
With Sheet1.QueryTables.Add(Connection:= _
        "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=" & fopen & _
        ";Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Datab" & _
        "ase Password="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global " & _
        "Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;J" & _
        "et OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False" _
        , Destination:=Range("A3"))
    .CommandType = xlCmdTable
    .CommandText = Array("FHPAINT$")
    .Name = strFile
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = True
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .SourceDataFile = fopen
    .Refresh BackgroundQuery:=True
End With
Sizeit      'make sure buttons are sized correctly
Formatit    'make sure columns have correct formats
SizeCheck   'make sure the values are correct sizes based on mainframe requirements

End Sub

[b]
Sub SizeCheck()[/b]
Dim msg, style, title, help, ctxt, response, mystring, inlrow As Long, SpaceIt As String, padsize As Integer

msg = "Incorrect size in column"                    'define message
style = vbYesOnly + vbCritical                      'define buttons
title = "Invalid Field Length Error"                'message box title
inlrow = Sheet1.UsedRange.Rows.Count

'response = MsgBox(msg, style, title)
 'check the column sizes first
For counter = 2 To inlrow
    If Cells(counter, 1) <> "" Then     'not empty
        If Len(Cells(counter, 1)) <> 7 Then     '200ft sheet number
            response = MsgBox("Invalid column length in 200ft sheet", vbCritical + vbDefaultButton2 + vbYesOnly, "Field Length Error")
            Exit Sub
        End If
        
       
        If Len(Cells(counter, 2)) <> 7 Then     'work order number
            response = MsgBox("Invalid column length in Work Order Num", vbCritical + vbDefaultButton2 + vbYesOnly, "Field Length Error")
            Exit Sub
        End If
        
        If Len(Cells(counter, 3)) <> 3 Then     'Fire Hydrant Number
            If Cells(counter, 3).Value >= 1 Or Cells(counter, 3).Value <= 9 Then
                Cells(counter, 3).Value = "00" & Cells(counter, 3).Value
                response = MsgBox("Invalid column length in Work Order Num", vbCritical + vbDefaultButton2 + vbYesOnly, "Field Length Error")
            Exit Sub
             End If
             
             If Len(Cells(counter, 7)) < 9 Then
                    'contract number 8/11/08 per Melissa Sawyer, if this field is less than 9 characters pad the field with spaces
                    padsize = 9 - Len(Cells(counter, 7))
                    SpaceIt = padsize
                    Cells(counter, 7).Value = Cells(counter, 7).Value & Space(SpaceIt)
            response = MsgBox("Invalid column length in Reported By column", vbCritical + vbDefaultButton2 + vbYesOnly, "Field Length Error")
            Exit Sub
        End If
        
        If Len(Cells(counter, 8)) <> 6 Then     'Inspection Date
            response = MsgBox("Invalid column length in Painted Date Column", vbCritical + vbDefaultButton2 + vbYesOnly, "Field Length Error")
            Exit Sub
        End If
        
        If Len(Cells(counter, 9)) <> 36 Then     'Valid Conditions
            response = MsgBox("Invalid column length in Valid Conditions Column", vbCritical + vbDefaultButton2 + vbYesOnly, "Field Length Error")
            Exit Sub
        End If
            
        End If
        
    End If
    
Next counter
End Sub

What I wanted to do was to import the entire sheet and then check each column returning the corresponding error message if there was a problem. Instead what appears to happen is that the file to import is selected and before the data actually paste into my sheet, it gives me an error message on the first column. I click ok and then the paste completes and the remaining if statements are not evaluated. The column is wrong

 

[SkipVought]

"What is the best way to check and see if a column has the correct number of digits "

What does that mean?

"The column is wrong"

What does that mean?

Please don't just throw your code out there. Please be specific. Exactly, what is the problem?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Lhuffst]

I apologize for the delay in responding--Was tied up on another project. I was trying to figure out

Correct number of digits -
This is equivalent to a field length. For example, Column one must have 7 numbers.
Put up an error message if it is longer or shorter.

What I did in the sample was intentionally make the column have the wrong amount of digits (8) to see if the error message would display.

I wasn't sure if this was the best way to handle something like this. Is there a way to preset the column size like you do in Access or VB by telling the maximum length?
Thanks again

 

[SkipVought]

Hi,

1. Why are you ADDING a querytable with code? This ought to be a ONE TIME event, after which you Sheet1.QueryTables(1).Refresh False

2. Here's your code simplified a tad. Also what are going to do with varaible response?

Sub SizeCheck()
    Dim msg, style, title, help, ctxt, response, mystring
    Dim inlrow As Long, SpaceIt As String, padsize As Integer
    Dim iCol As Integer, sMSG As String
    
    msg = "Incorrect size in column"                    'define message
    style = vbYesOnly + vbCritical                      'define buttons
    title = "Invalid Field Length Error"                'message box title
    inlrow = Sheet1.UsedRange.Rows.Count
    
    'response = MsgBox(msg, style, title)
     'check the column sizes first
    For counter = 2 To inlrow
        For iCol = 1 To Sheet1.UsedRange.Columns.Count
            If Cells(counter, iCol) <> "" Then
                With Sheet1.Cells(1, iCol)
                    Select Case iCol
                        Case 1, 2
                            If Len(.Value) <> 7 Then
                                sMSG = "Invalid column length in " & .Value & " ROW:" & counter
                                GoTo ErrorMsg
                            End If
                        Case 3
                            If Len(.Value) <> 3 Then
                                If .Value >= 1 And .Value <= 9 Then
                                    .Value = Format(.Value, "000")
                                    sMSG = "Invalid column length in " & .Value & " ROW:" & counter
                                    GoTo ErrorMsg
                                End If
                            End If
                        Case 7
                            If Len(.Value) <> 9 Then
                                .Value = Format(.Value, "000000000")
                                sMSG = "Invalid column length in " & .Value & " ROW:" & counter
                                GoTo ErrorMsg
                            End If
                        Case 8
                            If Len(.Value) <> 6 Then
                                sMSG = "Invalid column length in " & .Value & " ROW:" & counter
                                GoTo ErrorMsg
                            End If
                        Case 9
                            If Len(.Value) <> 36 Then
                                sMSG = "Invalid column length in " & .Value & " ROW:" & counter
                                GoTo ErrorMsg
                            End If
                    End Select
                End With
            End If
        Next
        Exit Sub
ErrorMsg:
    response = MsgBox(sMSG, vbCritical + vbDefaultButton2 + vbYesOnly, "Field Length Error")

End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

Correct number of digits -
This is equivalent to a field length. For example, Column one must have 7 numbers.
Put up an error message if it is longer or shorter.

Excel does have a data validation feature. (Data, Validation)
And a function to circle all data that does not meet the validation rule. (a toolbar button is available but not on standard menus/toolbars)

In code:

    With Columns("B:B").Validation
        .Delete
        .Add Type:=xlValidateTextLength, AlertStyle:=xlValidAlertStop, _
        Operator:=xlLessEqual, Formula1:="7"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    ActiveSheet.CircleInvalid

Gavin