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 ...
Macros and VBA
  • Man Fai Chan's avatar
    Man Fai Chan
    Jul 25, 2018

    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
Jul 25, 2018

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
Jul 25, 2018

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
    Jul 26, 2018

    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

     

Resources