Record varying length of text in cells

[vishal.bht]

I am importing a text file into excel as delimited and checking the format of the imported rows and columns..there are three format checks.

1. Character format : I was checking it using the legnth of the field..for e.g. if the field is a char(6) format then i m checking it as
If Len(cell.Value) > 6 Then Worksheets("Review").Range("D5").Value = "FAIL" Else Worksheets("Review").Range("D5").Value = "PASS"this is not working for me..even if i pass a field having legnt more than 6 the result is going in as pass..

2. Date format: this is working fine

3. decimal format : not very much sure how to check this....

can u please help with the 1st issue...thx..i hv posted my code below

Private Sub CommandButton1_Click()
Dim varFileName
Dim lastrow As Long
Dim cell As Range
Dim xlrng As Range
Dim xlrng1 As Range
varFileName = Application.GetOpenFilename("Text Files (*.txt), *.txt")
Sheets("Asset").Range("A3:DQ1000").Clear
'Sheets("Review").Range("A1:DQ1000").Clear
If TypeName(varFileName) = "String" Then
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & varFileName _
, Destination:=Range("A3"))
.Name = "Extract"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2)
.TextFileOtherDelimiter = "~"
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False

' lastrow = Sheets("Asset").Cells(Rows.Count, 1).End(xlUp).Row
'Set xlrng = Sheets("Asset").Range("M4:M" & lastrow)
'For Each cell In xlrng
' cell.NumberFormat = "0#.000"
'Next cell

lastrow = Sheets("Asset").Cells(Rows.Count, 1).End(xlUp).Row
Set xlrng = Sheets("Asset").Range("A4:A" & lastrow)
For Each cell In xlrng
If Len(cell.Value) > 6 Then Worksheets("Review").Range("D5").Value = "FAIL" Else Worksheets("Review").Range("D5").Value = "PASS"
' MsgBox Len(cell.Value)
Next cell

Set xlrng = Sheets("Asset").Range("B4:B" & lastrow)
For Each cell In xlrng
If Len(cell.Value) <= 8 Then Worksheets("Review").Range("D6").Value = "PASS" Else Worksheets("Review").Range("D6").Value = "FAIL"
Next cell
Set xlrng = Sheets("Asset").Range("C4:C" & lastrow)
For Each cell In xlrng
If Len(cell.Value) <= 6 Then Worksheets("Review").Range("D7").Value = "PASS" Else Worksheets("Review").Range("D7").Value = "FAIL"
Next cell
Set xlrng = Sheets("Asset").Range("D4:D" & lastrow)
For Each cell In xlrng
If Len(cell.Value) <= 6 Then Worksheets("Review").Range("D8").Value = "PASS" Else Worksheets("Review").Range("D8").Value = "FAIL"
Next cell
Set xlrng = Sheets("Asset").Range("E4:E" & lastrow)
For Each cell In xlrng
If Len(cell.Value) <= 6 Then Worksheets("Review").Range("D9").Value = "PASS" Else Worksheets("Review").Range("D9").Value = "FAIL"
Next cell
Set xlrng = Sheets("Asset").Range("F4:F" & lastrow)
For Each cell In xlrng
If Len(cell.Value) <= 6 Then Worksheets("Review").Range("D10").Value = "PASS" Else Worksheets("Review").Range("D10").Value = "FAIL"
Next cell
Set xlrng = Sheets("Asset").Range("G4:G" & lastrow)
For Each cell In xlrng
If Len(cell.Value) <= 6 Then Worksheets("Review").Range("D11").Value = "PASS" Else Worksheets("Review").Range("D11").Value = "FAIL"
Next cell



Range("A4").Select
Range(Selection, Selection.End(xlToRight)).Select
'Range(Selection, Selection.End(xlRight)).Select
'MsgBox TypeName(Range("A4").Value)
Selection.Copy
' MsgBox Len(Range("M4").Value)
Worksheets("Review").Range("C124").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=True


End With
End If
End Sub

I have removed some part of the code as it was quite legnthy....the part removed is format check only

I identified the problem but cannot find the solution....the objective is to check a complete row say ("A1:A300") for the legnth of the values in these cells..whenever the legnth of any value in these cells exceed 6 it should update another sheet "Review" cell as Fail else it should update it as Pass...with the current logic it is traversing all the records..but i think it is considering only the last value where it stops and compares it...if in between it would find any legnth greater than 6 that would be ignored...i want between A1 to A300 if the legnth of any value exceeds 6 it should be falied...could anybody pls help ...thx in advance
:banghead: :banghead: :banghead: :dunno

[Bob Phillips]

Try



If Trim(Len(cell.Value)) > 6 Then
Worksheets("Review").Range("D5").Value = "FAIL"
Else
Worksheets("Review").Range("D5").Value = "PASS"
End If