If Else Statement Hangs my PC

[fah_metal]

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

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.

 

[Damon Ostrander]

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]

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

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]

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