Maximum Columns in Worksheet

L

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?

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
 
T

Toppers

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

=Column(HP1) will return column number

HTH
 
L

Len

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
 
T

Toppers

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
 
L

Len

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
 
S

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?
Click to expand...

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?
Click to expand...

=COLUMN(...)
 
L

Len

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
 
D

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:="<>"
 
L

Len

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
 
G

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
 
Top