Forum Discussion

Lorenzo Kim's avatar
Lorenzo Kim
Bronze Contributor
Jul 25, 2018

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 if

    I am not sure if there is other easier way to do. Hope that it can help you. 

  • Man Fai Chan's avatar
    Man Fai Chan
    Iron Contributor

    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. 

    • Lorenzo Kim's avatar
      Lorenzo Kim
      Bronze Contributor

      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 FUNCTION


       

      • Lorenzo Kim's avatar
        Lorenzo Kim
        Bronze Contributor

        The 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

         

    • Lorenzo Kim's avatar
      Lorenzo Kim
      Bronze Contributor
      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
      • Lorenzo Kim's avatar
        Lorenzo Kim
        Bronze Contributor
        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

Resources