PDA

View Full Version : Problem with my vba code



lucpian
04-09-2008, 07:34 PM
Hi All,

I am having problem with a code I wrote that was working. The problem is that the template that contains this code will be used for both un-orderly and orderly headings with the headings placed at various position on the sheet. My code was to validate various length and it works perfectly with orderly headings, but not with un-orderly headings. Here is my code:


Function CheckLength(columnname As Integer, length As Integer)
Dim rowcount
Dim R
rowcount = Range("A65536").End(xlUp).Row
For R = 2 To rowcount

strVal = LTrim(RTrim(Sheet1.Cells(R, columnname).Value))
'MsgBox Len(strVal)
Sheet1.Cells(R, columnname).Value = strVal
If strVal <> "" And (Not (Len(strVal) = length)) Then
Sheet1.Cells(R, columnname).Interior.ColorIndex = 6
End If
Next

End Function

Sub LengthValidation()
CheckLength 1, 13
CheckLength 8, 4

End Sub



Please, I need help because I keep having errors when this un-orderly data are imported into the template.

Thanks all for all your help.

Lucpian

Aussiebear
04-10-2008, 01:34 AM
Lucipan, when you say "un-orderly", do you mean unsorted?

Charlize
04-10-2008, 02:36 AM
Are the headings always the same (if you use any). If so, check on the heading and based on the heading you say how wide the column can be.

Charlize

Charlize
04-10-2008, 03:14 AM
You could give this a try (always on a backup copy off course). Give the headers name instead of the column number.Function CheckLength(ColumnHeader As String, MyLength As Long)
Dim rowcount As Long
Dim R As Long
Dim c As Range
Dim strVal As String
'This is when they reposition the headers
'but still at row 1
With ActiveSheet.Range("A1:IV1")
Set c = .Find(ColumnHeader, LookIn:=xlValues)
If Not c Is Nothing Then
'This is adapted to count the no of rows in active column
rowcount = ActiveSheet.Range(Split(c.Address, "$")(1) & "65536").End(xlUp).Row
For R = 2 To rowcount
strVal = LTrim(RTrim(ActiveSheet.Cells(R, c.Column).Value))
'MsgBox Len(strVal)
ActiveSheet.Cells(R, c.Column).Value = strVal
If strVal <> "" And (Not (Len(strVal) = MyLength)) Then
ActiveSheet.Cells(R, c.Column).Interior.ColorIndex = 6
End If
Next R
Else
MsgBox "Header : - " & ColumnHeader & " - not found !", vbInformation
End If
End With
End Function

Sub LengthValidation()
CheckLength "Name", 13
CheckLength "Zipcode", 4
'This one doesn't exists so msgbox would pop up
CheckLength "What", 8
End Sub
Charlize