Does Column Exist?

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,723
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
Code:
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?
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,934
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Nov 9, 2002
Messages
2,723
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,934
Office Version
  1. 365
Platform
  1. Windows
How about
Code:
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

Well-known Member
Joined
Nov 9, 2002
Messages
2,723

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,934
Office Version
  1. 365
Platform
  1. Windows
Not a brilliant way , because it won't catch everything
Code:
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

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

There is surely a simpler way than this:

Code:
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

Well-known Member
Joined
Nov 9, 2002
Messages
2,723
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
Code:
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

Well-known Member
Joined
Nov 9, 2002
Messages
2,723
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

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows
You're welcome, but it needs a tweak:

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,609
Members
414,081
Latest member
penguin23

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top