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.
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

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,454
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,052
Messages
5,599,511
Members
414,315
Latest member
Yolanda5050

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