# If Else Statement Hangs my PC

#### fah_metal

##### New Member
What am i trying to do??

Say there are 10 bags on the floor. Each bag contains a mixture of fruits in them.
I only want the bags which contain a mixture of apple,banana and pineapple the rest i want to ignore.

eg: say one bag contains apple,banana and peach i will ignore the bag, but say one bag has apple,banana and pineapple,
i will take the bag.

IN EXCEL:
copy the cells from sheet 1 which contains the exact data(apple,banana,pineapple) along with qty and paste it in sheet 2.

IF i try this code the computer just hangs for some reason for 2 mins
and then gives me the error:
"run type error 13 type mismatch"

HERE IS THE CODE THAT I TRIED

Code:
``````Sub compile_One()
i = 1
j = 0
'k = 1
For i = 1 To 2
'If Worksheets(1).Cells(rows, 1).Value = "" Then
'Exit For
'Else
If Worksheets(1).Range("a" & i).Value = "apple" Then
'k = i
j = i
i = i + 1
Else
End If
'Next i 'GIVES AN ERROR DONT KNOW WHY???
'Exit For
If Worksheets(1).Range("a" & i).Value = "banana" Then
i = i + 1
Else
End If
'Next i
'Exit For
If Worksheets(1).Range("a" & i).Value = "pineapple" Then
'End If
'For j = j To 10
Worksheets(2).Range(Rows, 1).Value = Worksheets(1).Range("a" & j).Value 'HANGS HERE USE F8 TO SEE
Worksheets(2).Range(Rows, 1).Value = Worksheets(1).Range("b" & j).Value
j = j + 1
Worksheets(2).Range(Rows, 1).Value = Worksheets(1).Range("a" & j).Value
Worksheets(2).Range(Rows, 1).Value = Worksheets(1).Range("b" & j).Value
j = j + 1
Worksheets(2).Range(Rows, 1).Value = Worksheets(1).Range("a" & j).Value
Worksheets(2).Range(Rows, 1).Value = Worksheets(1).Range("b" & j).Value
Else
End If
'Exit For
Next i
End Sub``````

i am very new to this type of coding please let me know as to what went wrong.

Thanks.

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Hi fah_metal,

Well, without looking at the code too closely, I did notice that you have two instances of i = i + 1 inside your For i loop. Modifying a loop index inside a loop is normally considered a "no no".

Damon

Ive Modified the code a bit but it wont run as it gives me the
next without for
error.

Here's The Code

Code:
``````Sub compile_Two()
j = 0
For i = 1 To 10
If Worksheets(1).Range("a" & i).Value = "apple" And Worksheets(1).Range("a" & i + 1).Value = "banana" And Worksheets(1).Range("a" & i + 2).Value = "pineapple" Then
j = i
Else
Next i 'getting error "next without for" CAN YOU HELP ME WITH THIS LOOP PLEASE???
End If
Dim rows
For rows = 1 To 5
If Worksheets(1).Cells(rows, 1).Value = "" Then
MsgBox "There is a blank space", vbDefaultButton1
Exit For
Else
Worksheets(2).Cells(rows, j).Value = Worksheets(1).Cells(rows, j).Value             'hangs for 2mins
Worksheets(2).Cells(rows, j + 1).Value = Worksheets(1).Cells(rows, j + 1).Value  'then gives the error
Worksheets(2).Cells(rows, j + 2).Value = Worksheets(1).Cells(rows, j + 2).Value  'runtime error 13 type mismatch
End Sub``````

I wonder if functions will work here ???

Hi,
I found a snag in your code. I'm not sure if this is just a sample of what you're really doing or if it's the whole thing, but it's not hanging up on me or giving a runtime error... Try this and see if it's any better.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> compile_Two()
<SPAN style="color:#00007F">Dim</SPAN> j <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, rows <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
j = 0
<SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> 10
<SPAN style="color:#00007F">If</SPAN> Worksheets(1).Range("a" & i).Value = "apple" And Worksheets(1).Range("a" & i + 1).Value = "banana" And Worksheets(1).Range("a" & i + 2).Value = "pineapple" <SPAN style="color:#00007F">Then</SPAN>
j = i
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>

<SPAN style="color:#00007F">For</SPAN> rows = 1 <SPAN style="color:#00007F">To</SPAN> 5
<SPAN style="color:#00007F">If</SPAN> Worksheets(1).Cells(rows, 1).Value = "" <SPAN style="color:#00007F">Then</SPAN>
MsgBox ("Cell  " & Cells(rows, 1).Address(0, 0) & "  is blank."), vbDefaultButton1
<SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">For</SPAN>
<SPAN style="color:#00007F">Else</SPAN>
Worksheets(2).Cells(rows, j).Value = Worksheets(1).Cells(rows, j).Value
Worksheets(2).Cells(rows, j + 1).Value = Worksheets(1).Cells(rows, j + 1).Value
Worksheets(2).Cells(rows, j + 2).Value = Worksheets(1).Cells(rows, j + 2).Value
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">Next</SPAN> rows
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Hope it helps,
Dan

Replies
1
Views
98
Replies
4
Views
205
Replies
1
Views
187
Replies
15
Views
250
Replies
4
Views
164

1,219,569
Messages
6,149,042
Members
450,853
Latest member
xtiinctt

### 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.

### Which adblocker are you using?

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

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