Code not working in newer Excel Build

Uzziss

New Member
Joined
Nov 1, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,884
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
What is the value of RowNum when it errors?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,014
Messages
5,599,345
Members
414,306
Latest member
Dennis_vdw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top