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?

 

[Fluff]

I'm guessing that your "Blank workbook" has some data in row 1, otherwise the code won't find any columns.
Could you show us what you have in row 1?

 

[NdNoviceHlp]

There is nothing in row 1. I trialled it with data in data in row 1, same wrong result. I'm just trying to identify if the user's input actually represents a column by letter. I don't really care how to achieve it if U have a different approach. Thanks for your assistance. Dave

 

[Fluff]

How about

Sub RealColumn()
Dim Outputcolstr As String, Lbstr As Variant, Cnt As Integer, Cfind As Range
Outputcolstr = "D,7,11,U" 'User enters via input box
Lbstr = Split(Outputcolstr, ",")
For Cnt = 0 To UBound(Lbstr)
   With Sheets("Sheet1")
      If IsNumeric(Lbstr(Cnt)) Then
         .Columns(CInt(Lbstr(Cnt))).Select
      Else
         .Columns(Lbstr(Cnt)).Select
      End If
   End With
Next Cnt
End Sub

 

[NdNoviceHlp]

Thanks Fluff. I guess I wasn't clear. The user may enter "AB1" or some other string that doesn't represent a column. I'm trying to identify these errors. Dave

 

[Fluff]

Not a brilliant way , because it won't catch everything

Sub RealColumn()
Dim Outputcolstr As String, Lbstr As Variant, Cnt As Integer, Cfind As Range
Outputcolstr = "D,B1,11,U" 'User enters via input box
Lbstr = Split(Outputcolstr, ",")
For Cnt = 0 To UBound(Lbstr)
   If Not IsNumeric(Lbstr(Cnt)) Then
      If IsNumeric(Right(Lbstr(Cnt), 1)) Then
         MsgBox "invalid ref"
         Exit Sub
      End If
   End If
Next Cnt
End Sub

 

[shg]

There is surely a simpler way than this:

Sub Test()
  Dim vs            As Variant

  For Each vs In Split("D F HU7 U 22 zz")
    Debug.Print vs, IsAColumn(vs)
  Next vs
End Sub

Function IsAColumn(ByVal vsInp As Variant) As Boolean
  vsInp = UCase(vsInp)
  IsAColumn = vsInp Like Replace(String(Len(vsInp), "@"), "@", "[A-Z]") And _
              (Len(vsInp) < 3 Or vsInp <= "XFD")
End Function

 

[NdNoviceHlp]

I came up with this spagettied fix. Excel generates an error if there's no Column address. Shg thanks for your contribution. I'll try it out and post. 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)
On Error GoTo below
TempStr = Sheets("sheet1").Columns(Lbstr(Cnt)).Address
GoTo below2
below:
On Error GoTo 0
MsgBox "Column letter:   " & Lbstr(Cnt) & "   is not available!"
Exit Sub
below2:
If IsNumeric(Lbstr(Cnt)) Then
MsgBox "Enter Letters NOT Numbers for:   " & Lbstr(Cnt)
Exit Sub
End If
Next Cnt
End Sub

 

[NdNoviceHlp]

Shg it works a treat. It seems to capture everything. Thanks Fluff. Your code was somewhat successful but did miss some possibilities.Thanks to both U and Shg for your time. Have a nice day. Dave

 

[shg]

You're welcome, but it needs a tweak:

Function IsAColumn(ByVal vsInp As Variant) As Boolean
  vsInp = UCase(vsInp)
  IsAColumn = vsInp Like Replace(Left(String(Len(vsInp), "@"), 3), "@", "[A-Z]") And _
              (Len(vsInp) < 3 Or vsInp <= "XFD")
End Function