• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

If, Then syntax

R

Raesu

Member
Greetings,


This code highlights "Else" and says "Else without If". I am always running into little issues with If, Then statements, and it seems every time I do it I have to spend 15 minutes playing around with it until it works. No success this time :( It is comparing values from 2 ranges, and assigning a region to a column 5 cells over. ZipRef is an array of ranges. Any idea whats wrong? Thanks for any help.

[pre]
Code: 
For Each cell In Zips
For Each code In ZipRef(i)
If cell.Value >= code.Value And cell.Value <= code.Offset(0, 1).Value Then
Select Case i
Case 1
cell.Offset(0, 5).Value = "MW"
Case 2
cell.Offset(0, 5).Value = "West"
Case 3
cell.Offset(0, 5).Value = "SE"
Case 4
cell.Offset(0, 5).Value = "NE"
i = i + 1
Exit For
Else
Next code
End If
Next cell
[/pre]
 
Raesu


Have a try of this modified code below:

[pre]
Code: 
For Each cell In Zips
For Each code In ZipRef(i)
If cell.Value >= code.Value And cell.Value <= code.Offset(0, 1).Value Then
Select Case i
Case 1
cell.Offset(0, 5).Value = "MW"
Case 2
cell.Offset(0, 5).Value = "West"
Case 3
cell.Offset(0, 5).Value = "SE"
Case 4
cell.Offset(0, 5).Value = "NE"
End Select
i = i + 1
Exit For
Else
'Next code
End If
Next
Next
[/pre]

Note the Extra lines I've added
 
Hi ,


To explain why your code was giving an error , the "For ... Next" statement and the "If ... End If" statement were interspersed , not nested.


The "For ... Next" , "Select ... End Select" , "If ... End If" , "Do ... Loop" are all what are called compound statements ; they have a beginning part and an ending part , and they can have other statements within them.


So when a "For ..." part is met by the compiler , and then an "If ... " part is met , the compiler will expect to come across an "End If" part before it meets a "Next" part. This is called nesting i.e. one compound statement is wholly inside another compound statement. When there is a mix-up , it is an error.


Narayan
 
Awesome, thanks for the help. That makes sense and I do "know" how to nest statements but it was getting late!
 
Any idea why this does not work?

[pre]
Code: 
For Each cell In Zips
If Not WorksheetFunction.IsNumber(cell.Value) Then Next cell
[/pre]
Im trying to have it skip over cells in Zips that are strings. It says "Next without For", although For its right in a for loop...
 
Presumably, the rest of code would look like:

[pre]
Code: 
For Each cell In Zips
If Not WorksheetFunction.IsNumber(cell.Value) Then Next cell
'Some other stuff
Next cell
Note that you have 2 "next cell" callouts? That's bad. Perhaps this?

For Each cell In Zips
If WorksheetFunction.IsNumber(cell.Value) Then
'Do something
End If
Next cell
[/pre]
 
While its compiling, I'm just baffled as to why I can't use one line to skip a cell if its a string. At this point its not getting confused about other Next cell callouts.


I used your second recommendation and it works, but it complicated my loops a bit so now I have to do a bit more testing.


Ive read before that when you have a big If statement like that with lots of code between, you can use an If Not and turn it into just one line. Thanks for the help.
 
2nd alternative:

[pre]
Code: 
For Each cell In Zips
If Not WorksheetFunction.IsNumber(cell.Value) Then Goto SkipThis
'Some other stuff
SkipThis:
Next cell
[/pre]
 
I have it working great, but I have 45k lines of zip codes to check and this macro is currently on hour 2 of processing the regions. each region has lists of upper and lower limits, which are the code.Value and code.Offset(0,1).Value.

Any creative ideas to make this more efficient? I thought this wasn't bad as is, but speeding it up would be helpful. Can always run it at home on my 6-core desktop ;)

[pre]
Code: 
Dim Zips As Range, ZipRef(1 To 4) As Range
Dim i As Integer
Dim Found As Boolean

Set Zips = Worksheets("Report 1").Range("N4", Cells(1048576, 14).End(xlUp))
Set ZipRef(1) = Worksheets("ZipRef").Range("F3", "F556")
Set ZipRef(2) = Worksheets("ZipRef").Range("I3", "I263")
Set ZipRef(3) = Worksheets("ZipRef").Range("N3", "N1139")
Set ZipRef(4) = Worksheets("ZipRef").Range("S3", "S890")

For Each cell In Zips
If IsNumeric(cell) Then
Found = False
For i = 1 To 4
For Each code In ZipRef(i)
If cell.Value >= code.Value And cell.Value <= code.Offset(0, 1).Value Then
Select Case i
Case 1
cell.Offset(0, 5).Value = "MW"
Found = True
Case 2
cell.Offset(0, 5).Value = "West"
Found = True
Case 3
cell.Offset(0, 5).Value = "SE"
Found = True
Case 4
cell.Offset(0, 5).Value = "NE"
Found = True
End Select
Exit For
End If
Next code
If Found Then Exit For
Next i
End If
Next cell

MsgBox "DONE!"

End Sub
[/pre]
 
Hi ,


The first and the last statements in any production-run procedure should be :


First : Application.ScreenUpdating = False


Last : Application.ScreenUpdating = True


The first one makes a significant difference.


Narayan
 
Hi ,


More significant than any changes you can make to code is to shift to formulae. The comparison and selection of 4 regions may be an easy task using formulae.


Can you clarify the following ?


In the comparison of each "cell" from "Zips" , will the result have multiple matches or only one match i.e. if you take a particular value from "Zips" , and try to match it with any "code" in ZipRef(1) , ZipRef(2) , ZipRef(3) , ZipRef(4) , will there be only one match or can there be many matches ?


Can you post a sample workbook with say 100 rows from both your "Report 1" worksheet and your "ZipRef" worksheet ?


Narayan
 
@Raesu


You are working on the cells individually and hence each time you go through the Excel/VBA interface, which is lots, it is slow


Loading all the ranges into a number of arrays and working on them in the arrays is much much much faster


If you want to send me the file, I'd be happy to have a look at it for you.
 
Heres what my zips looks like, and the zip references for MW and West. Now that I got this first version done, I'm going to do a lot of optimizing of the zip refs. A lot of the ranges are consecutive, but some are not. (37110 for example is not in MW.) I have no idea why these zip references were set up so poorly!


Sorry for format getting reset, left column is my zip to assign, then MW to the right, then West.

[pre]
Code: 
MW	                    WEST
2139		From	To		From	To
66085		37010	37109		83201	83888
7927		37111	37165		88901	89012
92121		37167	37250		89013	89013
30033		37901	37901		89014	89021
18103		37903	37908		89022	89022
94085		37910	37911		89023	89027
27101		37913	37913		89028	89028
95616		37925	37930		89029	89029
71209
78229
31419
[/pre]
 
Hi ,


Please correct me if I am wrong.


I copied your first column of data ( 2139 , 66085 ,... ) into column A , from cell A5 downwards. The other columns of data were copied to other areas of the same worksheet , B5:C12 and D5:E12.


The job , as I understand it , is to verify whether each cell value in column A falls between the values in the ranges B5:C12 , D5:E12 and so on.


In cell F5 , I put in the following formula :


=SUMPRODUCT((A5:A5)*($B$5:$B$12<=A5)*($C$5:$C$12>=A5))


This need not be entered as an array formula.


This returns either 0 , if the value in cell A5 does not fall between any of the values in the range B5:C12 , or the value in cell A5 itself , if the value in cell A5 does fall between any of the values in the range B5:C12.


If you now check for whether the result is >0 , you can decide that the value either falls between or does not fall between the values in the given ranges.


Based on this , you can select "MW" for those entries which fall within the given ranges.


Similar formulae can be used for the other ranges "West" , "SE" and "NE".


I have uploaded your file with these formulae. Check it out.


https://skydrive.live.com/?id=754467BA13646A3F!116&cid=754467ba13646a3f&#!/view.aspx?cid=754467BA13646A3F&resid=754467BA13646A3F%21116


Narayan
 
@Nara, wow...would NOT have come up with that on my own. I had to use evaluate formula for 15 minutes until I got a mild understanding of that. Thanks to both of you for your help!
 
You must log in or register to reply here.
Back
Top