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