Hi
I am using some code to randomly select 40 questions from a list of 241 that I have. Everyone in the office uses office 365, however some of us are on build 1908 and some are on 2002. Anyone that uses version 2002 can't run the VBA. It will run fine the first time and then fail with a run time error 13 type mismatch. Below is the code. On the newer build of excel it fails at the bold line. Has something changed on the new build that changes how Count IF works? We have checked and everyone has the same reference libraries in VBA so I am stumped.
Private Sub CommandButton1_Click()
Dim i, RowNum
Sheets("MockE").Range("A4:A44").ClearContents
Sheets("MockE").Range("B4:B44").ClearContents
Sheets("Exam Ans").Range("C4:C44").ClearContents
Sheets("MockE").Range("C4:C44").ClearContents
For i = 1 To 40 'number of times it looks for a question
generate:
RowNum = Application.RoundUp(Rnd() * 241, 0) 'number is the number of questions you have
If Application.CountIf(Sheets("MockE").[A4:C44], Sheets("EXAM QAs").Cells(RowNum, "A")) = 0 Then
Sheets("MockE").Range("A" & Rows.Count).End(xlUp).Offset(1).Value = Sheets("EXAM QAs").Cells(RowNum, "A").Value
Sheets("MockE").Range("B" & Rows.Count).End(xlUp).Offset(1).Value = Sheets("EXAM QAs").Cells(RowNum, "B").Value
Sheets("Exam Ans").Range("C" & Rows.Count).End(xlUp).Offset(1).Value = Sheets("EXAM QAs").Cells(RowNum, "C").Value
Range("A4:A44").Rows.AutoFit
Else
GoTo generate
End If
Next i
End Sub
I am using some code to randomly select 40 questions from a list of 241 that I have. Everyone in the office uses office 365, however some of us are on build 1908 and some are on 2002. Anyone that uses version 2002 can't run the VBA. It will run fine the first time and then fail with a run time error 13 type mismatch. Below is the code. On the newer build of excel it fails at the bold line. Has something changed on the new build that changes how Count IF works? We have checked and everyone has the same reference libraries in VBA so I am stumped.
Private Sub CommandButton1_Click()
Dim i, RowNum
Sheets("MockE").Range("A4:A44").ClearContents
Sheets("MockE").Range("B4:B44").ClearContents
Sheets("Exam Ans").Range("C4:C44").ClearContents
Sheets("MockE").Range("C4:C44").ClearContents
For i = 1 To 40 'number of times it looks for a question
generate:
RowNum = Application.RoundUp(Rnd() * 241, 0) 'number is the number of questions you have
If Application.CountIf(Sheets("MockE").[A4:C44], Sheets("EXAM QAs").Cells(RowNum, "A")) = 0 Then
Sheets("MockE").Range("A" & Rows.Count).End(xlUp).Offset(1).Value = Sheets("EXAM QAs").Cells(RowNum, "A").Value
Sheets("MockE").Range("B" & Rows.Count).End(xlUp).Offset(1).Value = Sheets("EXAM QAs").Cells(RowNum, "B").Value
Sheets("Exam Ans").Range("C" & Rows.Count).End(xlUp).Offset(1).Value = Sheets("EXAM QAs").Cells(RowNum, "C").Value
Range("A4:A44").Rows.AutoFit
Else
GoTo generate
End If
Next i
End Sub