Does Column Exist?

[NdNoviceHlp]

I've been trying to prevent user entry error for column entry. The following works for every column but "C" and column "C" does exist. I don't get it. Thanks for any assistance. Dave

Sub RealColumn()
Dim Outputcolstr As String, Lbstr As Variant, Cnt As Integer, Cfind As Range
Outputcolstr = "D,F,HU7,U" 'User enters via input box
Lbstr = Split(Outputcolstr, ",")
For Cnt = 0 To UBound(Lbstr)
With Sheets("Sheet1")
Set Cfind = .Columns.Find(What:=Lbstr(Cnt), After:=.Cells(1, 1), LookAt:=xlWhole, _
  SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If Cfind Is Nothing Then
MsgBox "Column: " & Lbstr(Cnt) & " doesn't exist!"
'Exit Sub
End If
End With
Next Cnt
End Sub

I pasted this code in a blank workbook and every column but "D" didn't exist?

 

[NdNoviceHlp]

Thanks again shg. It works just as well (better?) than your previous post. I appreciate your assistance. Dave

 

[shg]

One more variation -- compact, but I expect slower.

Sub Test()
  Dim vs As Variant
  
  For Each vs In Split("A b 2 A1 XFD XFE")
    Debug.Print vs, IsAColumn(vs)
  Next vs
End Sub

Function IsAColumn(ByVal vsInp As Variant) As Boolean
  On Error Resume Next
  IsAColumn = Range(Join(Array(vsInp, ":", vsInp), "")).Address = Columns(vsInp).Address
End Function

 

[NdNoviceHlp]

Seems to work as good or better(?) than the other 2 previous trials. I think that I arrived at the same end point as U with letting XL decide if it liked the address or not by using the error generated when it doesn't like the address. Although, I'm not quite clear on how your code works? Anyways, here's a clumsier version. Again, thanks for your assistance. Dave

Sub test()
Dim Cnt As Integer, Outputcolstr As String, LbStr As Variant
Outputcolstr = "A,b,2,A1,XFD,XFE,66,hh,u8"
LbStr = Split(Outputcolstr, ",")
For Cnt = 0 To UBound(LbStr)
If Not IsColumn(LbStr(Cnt)) Then
MsgBox "Column letter:  " & LbStr(Cnt) & "  is not available!"
'Exit Sub
End If
Next Cnt
End Sub

Function IsColumn(InputCol As Variant) As Boolean
'check if column exists. InputCol with Letter entry
Dim TempStr As String
On Error Resume Next
TempStr = ActiveSheet.Columns(InputCol).Address
If Err.Number <> 0 Then
On Error GoTo 0
IsColumn = False
Else
IsColumn = True
End If
End Function