Maximum Columns in Worksheet

[Guest]

The testing department wants me to assist them in a large project that should
be written in "C". However I will give it a shot!

They want a Worksheet that is going to require 426 to 434 Columns. First
Question: Is that possible?

Second Question (This might be an Excel Programming Question?) When the tell
me something is in Column HP, how can I confert that to the Field (Column)
Number?

Thanks for Assistance

Len

 

[Guest]

You will need Excel 2007 for more than 256 columns (the limit until this
release)

=Column(HP1) will return column number

HTH

 

[Guest]

Toppers;

Thanks for Responding.

I tried this little code, I got the error Message:

Sub or Function not defined.

Dim ReqColumn as String
Dim ColumnNo as Long

ReqColumn = Input("Give Me the Column")
ColumnNo = Column(ReqColumn) Error
ColumnNo = DD1 Error
ColumnNo = Column(DD) Error
ColumnNo = Column(DD,1) Error

I get the same error message on all five?

Also there is a "C" programmer that changed Excel in 1998 to expand to 384
columns. I do not know him, but I have seen the spreadsheet so I know it
worked. I know he made a lot of changes that I am not going to attempt!

Thanks Again

Len

 

[Guest]

in VBA ( I don't "speak" C):

Sub x()
Dim ReqColumn As Range
Dim ColumnNo As Long

Set ReqColumn = Application.InputBox("Give Me the Column", Type:=8)
ColumnNo = ReqColumn.Column
MsgBox ColumnNo

End Sub

 

[Guest]

Toppers;

Thanks for getting back again! I entered the code you sent me
and now I get a different error message. The Code I have for a test is shown
below.

Dim ColumnNo As Long
Dim ReqColumn As Range

Set ReqColumn = Application.InputBox("Give Me the Column", Type:=8)
Right After I enter the Column (HJ) and Depress the Enter Key, I get the
message Below and Hit The Enter Key!

ColumnNo = ReqColumn.
Column MsgBox ColumnNo

The reference you typed is not valid or you did not provide reference where
one was required!

Len

 

[Guest]

You need to enter a cell address (A1,HJ1) rather than just a column "address".

 

[Stan Brown]

Sat, 7 Jul 2007 07:40:01 -0700 from Len

The testing department wants me to assist them in a large project that should
be written in "C". However I will give it a shot!

They want a Worksheet that is going to require 426 to 434 Columns. First
Question: Is that possible?

Yes, with Excel 2007. No, with earlier versions.

Second Question (This might be an Excel Programming Question?) When the tell
me something is in Column HP, how can I confert that to the Field (Column)
Number?

=COLUMN(...)

 

[Guest]

Toppers:

It is too difficult to list all my code!

I have:

Dim rng as Range
Dim SelCol as String

SelCol = "HJ1"

If .AutoFilterMode Then .AutoFilterMode = False
Set rng = .Range("A1").CurrentRegion
With rng
.AutoFilter Field:=SelCol, Criteria1:="<>" Error Line
End With

Dosen't this force the search to Start at A1 or every column?

Thanks

Len

 

[Dave Peterson]

Field:=
wants to see a number--not a letter--not an address.

And this number is the column/field in the autofiltered range--not always the
column number for that column.

If you filtered D:J, then field:=2 would mean to filter on column E.

But since you're starting in column A, the column number of the worksheet and
the field number for the filtered range are the same.

So you could use this:

.AutoFilter Field:=.range(selcol).column, Criteria1:="<>"

 

[Guest]

Dave;
Thanks,
I knew some of what you informed me of. My major problem
is the syntax and what objects to call.

The girls want to enter the Column by what is display at
the top of the page. For example they want to enter HJ not 218. Thsi is
also a lot safer, less chance of making an error. My biggest problem is
taking "HJ" and returning 218. There have been several methods recommended
to me, but none have worked.

Thanks Again

Len

 

[Gord Dibben]

Function GetColNum(myColumn As String) As Integer
GetColNum = Columns(myColumn & ":" & myColumn).Column
End Function

=getcolnum("hj") returns 218


Gord Dibben MS Excel MVP

 

[Guest]

Gord;

Thanks a lot, this finally worked! Hope you have a good Sunday!

Len