Type Mismatch

[Patrick Simonds]

Can anyone tell me why the line rng(1, 4) = "X" below returns a Type Mismatch error? This should place an X in the 4column over from the

Private Sub CommandButton1_Click()

Application.ScreenUpdating = False

Dim SH As Worksheet
Dim rng
Dim arr As Variant
Const baseCell As String = "A200"

arr = Array("June - August", "September - November", "December - February", _
"March - May")

'This code is to run only on the sheet that was active when the macro was called

Range("A200").Select

If CheckBox1.Value = True Then
rng(1, 4) = "X"
End If

'This code is to run on all sheets

For Each SH In Sheets(arr)

Set rng = SH.Range(baseCell)

rng(1, 1).Value = TextBox1.Text
rng(1, 2).Value = TextBox2.Text
rng(1, 4).Value = TextBox3.Text

If OptionButton1.Value = True Then
rng(1, 3) = "Member"

ElseIf OptionButton2.Value = True Then
rng(1, 3) = "Officer"

ElseIf OptionButton3.Value = True Then
rng(1, 3) = "PMP"

ElseIf OptionButton4.Value = True Then
rng(1, 3) = "Officer/PMP"

ElseIf OptionButton5.Value = True Then
rng(1, 3) = "Guest"

ElseIf OptionButton6.Value = True Then
rng(1, 3) = "Guest Officer"

ElseIf OptionButton7.Value = True Then
rng(1, 3) = "Guest PMP"

End If

Next SH

Module2.Sort_June_August
Module2.Sort_September_November
Module2.Sort_December_February
Module2.Sort_March_May

Unload Add_New_Name

Application.ScreenUpdating = True

End Sub

 

[Leith Ross]

Hello Patrick,

Since your explaination was cut off, I assume you wanted Rng to refe
to the 4th cell to the right of "A200". Without the SET statemen
preceding the variable, Excel interprets the Rng(1, 4) as an array. Th
type mismatch occurs because Excel thinks you are assigning the strin
"X" to an array that has not been delcared.

There are a couple of fixes...

1) Remove the statement Range("A200").Select and use the following I
statement.

If CheckBox1.Value = True Then
Range("A200").Offset.(0, 4) = "X"
End If

2) Use a SET statement instead of Range("A200").Select.

Set Rng = Range("A200")

Sincerely,
Leith Ros