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

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

Thanks

"Niek Otten" schreef in bericht
...
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
|
|