If Else Statement Hangs my PC

fah_metal

New Member
Joined
Dec 14, 2004
Messages
12
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

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
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
 

fah_metal

New Member
Joined
Dec 14, 2004
Messages
12
Thanks For The Reply

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 ???
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
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">Next</SPAN> i  <SPAN style="color:#007F00">'You had your Next statement before you ended your If statement...</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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,036
Messages
5,856,959
Members
431,841
Latest member
jaybeem

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