Forum Discussion
VBA to trap error for Column entry
The SUB below can capture the errors for non-letters and more than 3 characters for Column entry but can not detect if beyond XFD is entered. like XXX - it just looped!
i.e. legit entry should be A to XFD only.
many many thanks
Sub MarkNonConformCell()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
Dim mcol As String
mcol = InputBox("Enter Column to check")
If IsLetter(mcol) = False Or Len(mcol) > 3 Then
MsgBox "Column input error!", vbCritical
Exit Sub
End If
Dim i As Long
For i = Cells(Rows.Count, mcol).End(xlUp).Row To 2 Step -1
If Len(Cells(i, mcol)) <> 3 Then Cells(i, mcol) = "1" & Cells(i, mcol)
Next i
Application.ScreenUpdating = True
Application.DisplayAlerts = True
On Error GoTo 0
End Sub
Function IsLetter(strValue As String) As Boolean
Dim intPos As Integer
For intPos = 1 To Len(strValue)
Select Case Asc(Mid(strValue, intPos, 1))
Case 65 To 90, 97 To 122
IsLetter = True
Case Else
IsLetter = False
Exit For
End Select
Next
End Function
The problem goes to the variable "mcol" when it is beyond XFD. You include a checking before the for-loop as below:
check_col = Cells(1,mcol).column
If check_col > 0 then
' it is good and okay to run the next steps
else
msgbox("Input does not refer to a column. ")
exit sub
end ifI am not sure if there is other easier way to do. Hope that it can help you.
The problem goes to the variable "mcol" when it is beyond XFD. You include a checking before the for-loop as below:
check_col = Cells(1,mcol).column
If check_col > 0 then
' it is good and okay to run the next steps
else
msgbox("Input does not refer to a column. ")
exit sub
end ifI am not sure if there is other easier way to do. Hope that it can help you.
Mr. Chan
You saved the day again!!
below now is the final SUB, thanks to you.
more power and good health..
Sub MarkNonConformCell()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
Dim mcol As String
mcol = InputBox("Enter Column to check")
check_col = Cells(1, mcol).Column
If check_col <= 0 Then
MsgBox "Column input error!", vbCritical
Exit Sub
End If
Dim i As Long
For i = Cells(Rows.Count, mcol).End(xlUp).Row To 2 Step -1
If Len(Cells(i, mcol)) <> 3 Then Cells(i, mcol) = "1" & Cells(i, mcol)
Next i
Application.ScreenUpdating = True
Application.DisplayAlerts = True
On Error GoTo 0
End Sub
**NO MORE NEED FOR THE IsLetter FUNCTIONThe following SUB can be tried by anyone who might need it.
to check if the Column Letter entered is legit...
Sub CheckColumnLetterEntry()
'courtesy of Mr. Man Fai Chan 7-26-2018
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
Dim mcol As String
mcol = InputBox("Enter Column Letter")
check_col = Cells(1, mcol).Column
If check_col <= 0 Then
MsgBox "Column Letter input error!", vbCritical
else
MsgBox "Column Letter is OK.."
End If
Application.ScreenUpdating = True
Application.DisplayAlerts = True
On Error GoTo 0
End Sub
- Mr. Chan I searched the net re:below but I can not find any article about it. Is it a VBA function? many thanks check_col = Cells(1,mcol).column
- Mr. Chan I searched the net re:below but I can not find any article about it. Is it a VBA function? many thanks check_col = Cells(1,mcol).column
