Forum Discussion
VBA to trap error for Column entry
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 if
I am not sure if there is other easier way to do. Hope that it can help you.
- 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
Cells(i,j).Column will return the column number for the cell.
So, Cells(1,"AA").column will return 27.
I tested that Cells(1,"XFD").column returns 16384 while Cells(1,"XFE").column returns nothing/null. So I consider the criteria ">0" for the checking of the existence of the column.
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
