Hi Bob,

Excel 2007 has 16384 columns, all other Excel versions have 256. That can't be changed.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Bob" wrote in message ...
| Hi,
|
| i have a list of questions of different kinds which are in one column (in
| sheet2).
| Starting from that list, i want to create (in sheet3) a long row containing
| all the kinds of questions with a number (like: openquestion1,
| openquestion2, closedquestion1 etc ..(i use dutch like 'vraag1' etc ..) .
|
| This works except when the amount of questions is greater than 256. Then i
| get an error at line:
| Worksheets("blad3").Cells(1, j + 1) = "vraag" & k
|
| In that case, the row goes till column IV (so if i calculate well: 26 +
| 9*26 -4 (IW,IX,IY,IZ) = 256) and then gives the error.
|
| I also tried with LONG instead of INTEGER. Same error.
|
| Here below the code.
|
| Any help would be appreciated.
| Thanks
| Bob
|
|
| Private Sub CommandButton1_Click()
| Dim i, j, k, h, numrec As Integer
| numrec =
| Application.WorksheetFunction.CountA(Sheets("blad2 ").Range("E:E")) - 1
| Worksheets("blad3").Cells(1, 1) = "login"
| For i = 5 To 4 + numrec
| j = j + 1
| If Worksheets("blad2").Cells(i, 1) = "hoofd genummerd" Or
| Worksheets("blad2").Cells(i, 1) = "hoofd niet genum." Then
| h = h + 1
| Worksheets("blad3").Cells(1, j + 1) = "vraagh" & h
| ElseIf Worksheets("blad2").Cells(i, 1) = "" Then
| k = k + 1
| Worksheets("blad3").Cells(1, j + 1) = "vraag" & k
| ElseIf Worksheets("blad2").Cells(i, 1) = "open genummerd" Or
| Worksheets("blad2").Cells(i, 1) = "open niet genum." Then
| k = k + 1
| Worksheets("blad3").Cells(1, j + 1) = "vraago" & k
| End If
| Next
| ActiveWorkbook.Names.Add Name:="data", RefersToR1C1:="=blad3!R1C1:R1C" & j +
| 1
| ActiveWorkbook.Names.Add Name:="vragen", RefersToR1C1:="=blad2!R4C1:R" &
| numrec + 4 & "C5"
| End Sub
|
|