Having trouble with the Dim and Set command in VBA, compile error "mismatch"

Question

Writing a short script in VBA to sort through data, essentially counting the number of names entered into a list, and printing them into a new column. However, the & in the row "Set Staff 1" is causing a compile error due to a mismatch. It seems to be caused by setting two ranges at the same time. I get the impression that there's a straightforward solution...

Sub Staffing() Dim Rng As Range Dim i As Long Dim Staff1 As Range Dim Staff2 As Range Dim Staff3 As Range Dim Staff4 As Range Dim Staff5 As Range Dim Staff6 As Range Dim Staff7 As Range While i <= 300 Set Rng = Range("J" & i) Set Staff1 = ("X" & i) Set Staff2 = ("AD" & i) Set Staff3 = ("AJ" & i) Set Staff4 = ("AP" & i) Set Staff5 = ("BB" & i) Set Staff6 = ("BH" & i) Set Staff7 = ("BN" & i) If Staff1 <> "" Then Rng.FormulaR1C1 = "0" i = i + 1 If Staff2 <> "" Then Rng.FormulaR1C1 = "1" i = i + 1 If Staff3 <> "" Then Rng.FormulaR1C1 = "2" i = i + 1 If Staff4 <> "" Then Rng.FormulaR1C1 = "3" i = i + 1 If Staff5 <> "" Then Rng.FormulaR1C1 = "4" i = i + 1 If Staff6 <> "" Then Rng.FormulaR1C1 = "5" i = i + 1 If Staff7 <> "" Then Rng.FormulaR1C1 = "6" i = i + 1 Else Stop End If Wend End Sub

Thanks in advance!

Thanks for everyone's patience. I had mad HUGE errors in writing this first code, but have fixed them thanks to your help and some of my own trial and error. The correct code is as follows. I'm sure now you'll be able to see what I was trying to do!

Sub StaffingNumbers() Dim Rng As Range Dim i As Long Dim Staff1 As Range Dim Staff2 As Range Dim Staff3 As Range Dim Staff4 As Range Dim Staff5 As Range Dim Staff6 As Range Dim Staff7 As Range Dim Staff8 As Range Dim Staff9 As Range Dim Staff10 As Range i = 3 While i <= 300 Set Rng = Range("J" & i) Set Staff1 = Range("X" & i) Set Staff2 = Range("AD" & i) Set Staff3 = Range("AJ" & i) Set Staff4 = Range("AP" & i) Set Staff5 = Range("AV" & i) Set Staff6 = Range("BB" & i) Set Staff7 = Range("BH" & i) Set Staff8 = Range("BN" & i) If Staff1 = "" Then Rng.FormulaR1C1 = "0" i = i + 1 ElseIf Staff1 <> "" And Staff2 = "" Then Rng.FormulaR1C1 = "1" i = i + 1 ElseIf Staff2 <> "" And Staff3 = "" Then Rng.FormulaR1C1 = "2" i = i + 1 ElseIf Staff3 <> "" And Staff4 = "" Then Rng.FormulaR1C1 = "3" i = i + 1 ElseIf Staff4 <> "" And Staff5 = "" Then Rng.FormulaR1C1 = "4" i = i + 1 ElseIf Staff5 <> "" And Staff6 = "" Then Rng.FormulaR1C1 = "5" i = i + 1 ElseIf Staff6 <> "" And Staff7 = "" Then Rng.FormulaR1C1 = "6" i = i + 1 ElseIf Staff7 <> "" And Staff8 = "" Then Rng.FormulaR1C1 = "7" i = i + 1 ElseIf Staff8 <> "" Then Rng.FormulaR1C1 = "8" i = i + 1 Else Stop End If Wend Set Rng = Nothing Set Staff1 = Nothing Set Staff2 = Nothing Set Staff3 = Nothing Set Staff4 = Nothing Set Staff5 = Nothing Set Staff6 = Nothing Set Staff7 = Nothing End Sub

The way you set the first rng should be a clue. Set Staff1 = Range("X" & i) — user4039065
– user4039065, Commented Aug 4, 2015 at 8:47
It also isn't clear what you want to do. Should there be a bunch of ElseIfinstead of If alfter the first If? — user4039065
– user4039065, Commented Aug 4, 2015 at 9:08

Máté Juhász · Accepted Answer · 2015-08-04 08:47:46Z

2

You missed the Range in that line, first line with set Set Rng = Range("J" & i) is correct, all the others should be similar too.

answered Aug 4, 2015 at 8:47

Máté Juhász

2,3552 gold badges24 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user4039065 · Accepted Answer · 2015-08-04 09:13:45Z

The logic of what you want to accomplish after the range assignments is not clear. Perhaps this is what you hope to chieve.

Dim i As Long Dim Staff1 As Range, Staff2 As Range, Staff3 As Range Dim Staff4 As Range, Staff5 As Range, Staff6 As Range Dim Staff7 As Range, Rng As Range While i <= 300 Set Rng = Range("J" & i) Set Staff1 = Range("X" & i) Set Staff2 = Range("AD" & i) Set Staff3 = Range("AJ" & i) Set Staff4 = Range("AP" & i) Set Staff5 = Range("BB" & i) Set Staff6 = Range("BH" & i) Set Staff7 = v("BN" & i) If Staff1 <> "" Then Rng.FormulaR1C1 = "0" i = i + 1 ElseIf Staff2 <> "" Then Rng.FormulaR1C1 = "1" i = i + 1 ElseIf Staff3 <> "" Then Rng.FormulaR1C1 = "2" i = i + 1 ElseIf Staff4 <> "" Then Rng.FormulaR1C1 = "3" i = i + 1 ElseIf Staff5 <> "" Then Rng.FormulaR1C1 = "4" i = i + 1 ElseIf Staff6 <> "" Then Rng.FormulaR1C1 = "5" i = i + 1 ElseIf Staff7 <> "" Then Rng.FormulaR1C1 = "6" i = i + 1 Else Stop End If Wend

It really isn't clear why the Stop statement is there. Perhaps you want to exit the Do While? That would be Exit Do.

I've found a solution myself thanks to your help, and some of my own trial and error. I had made large mistakes in the initial code. See my edit for the complete script.

R3uK · Accepted Answer · 2015-08-04 09:20:51Z

You forgot to add Range in Set Staff2 = Range("AD" & i) which should be Set Staff2 = Range("AD" & i)

Also, don't forget to free your objects at the end of the proc, by using Set Staff2 = Nothing

You also forgot to close most of your If statements with End If, I let it as it was because I don't know what you want to do with your code :

Sub Staffing() Dim Ws As Worksheet Dim Rng As Range Dim i As Long Dim Staff1 As Range Dim Staff2 As Range Dim Staff3 As Range Dim Staff4 As Range Dim Staff5 As Range Dim Staff6 As Range Dim Staff7 As Range i = 1 Set Ws = ThisWorkbook.Sheets("SheetNameHere") While i <= 300 With Ws Set Rng = .Range("J" & i) Set Staff1 = .Range("X" & i) Set Staff2 = .Range("AD" & i) Set Staff3 = .Range("AJ" & i) Set Staff4 = .Range("AP" & i) Set Staff5 = .Range("BB" & i) Set Staff6 = .Range("BH" & i) Set Staff7 = .Range("BN" & i) End With If Staff1.Value <> "" Then Rng.FormulaR1C1 = "0" i = i + 1 If Staff2 <> "" Then Rng.FormulaR1C1 = "1" i = i + 1 If Staff3 <> "" Then Rng.FormulaR1C1 = "2" i = i + 1 If Staff4 <> "" Then Rng.FormulaR1C1 = "3" i = i + 1 If Staff5 <> "" Then Rng.FormulaR1C1 = "4" i = i + 1 If Staff6 <> "" Then Rng.FormulaR1C1 = "5" i = i + 1 If Staff7 <> "" Then Rng.FormulaR1C1 = "6" i = i + 1 Else Stop End If Wend Set Ws = Nothing Set Rng = Nothing Set Staff1 = Nothing Set Staff2 = Nothing Set Staff3 = Nothing Set Staff4 = Nothing Set Staff5 = Nothing Set Staff6 = Nothing Set Staff7 = Nothing End Sub

Thanks for the response. I've added your suggestions. However, now I am getting a runtime error 1004 of method 'range' of object'_global'. Is this just a matter of defining what the Active Sheet should be somewhere?
On which line? Indeed referencing the sheet would be a great idea anyway! ;) Maybe you should set i=0 before looping, look amended code! ;)
The line is Set Rng = Range("J" & i). How exactly do I reference the sheet? Thanks!
Correction done, it was not i=0 but i=1 to initialize and I added the line to reference easily the sheet by name, give it a try after changing the name of the sheet! ;)
Thanks. I actually fixed the code completely now (see edit), without the reference line. I'll keep your advice in mind in future though!

Collectives™ on Stack Overflow

Having trouble with the Dim and Set command in VBA, compile error "mismatch"

3 Answers 3

Comments

1 Comment

5 Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

5 Comments

Related