IF Function ≈ If Statement ≈ Select Case Statement - Doesn't it???

[swallis]

[TABLE="width: 783"]
<TBODY>[TR]
[TD="class: xl24, width: 1044, bgcolor: transparent"][TABLE="width: 475"]
<COLGROUP><COL style="WIDTH: 475pt; mso-width-source: userset; mso-width-alt: 23149" width=633><TBODY>[TR]
[TD="class: xl25, width: 633, bgcolor: transparent"]I want to replace a nested IF worksheet function to allow expansion of my options. I couldn't puzzle out an IF statement that worked, but managed to cobble together a Select Case statement which does the job. The problem is that it takes over 50 seconds to run, compared to about 0.2 seconds for the IF function. I know a Vlookup function is a good alternative (in fact faster than IF), but I'm hoping someone can advise why I'm getting such a time difference between two processes doing essentially the same job?

[TABLE="width: 475"]
<COLGROUP><COL style="WIDTH: 475pt; mso-width-source: userset; mso-width-alt: 23149" width=633><TBODY>[TR]
[TD="class: xl25, width: 633, bgcolor: transparent"]The object is to enter a number in Column AA, based on information in the corresponding row in Column F. There are usually about 1000 rows of data. I'm enclosing the existing code and the slow alternative. Any help would be appreciated.[/TD]
[/TR]
</TBODY>[/TABLE]

[/TD]
[/TR]
</TBODY>[/TABLE]

[/TD]
[/TR]
[TR]
[TD="class: xl24, width: 1044, bgcolor: transparent"]Steve

Sub LKup7()
t = Timer
With Range(Cells(2, "a"), Cells(Rows.Count, "a").End(xlUp)).Offset(, 25)
    .FormulaR1C1 = "=if(or(RC6=""QLD"",RC6=""SA"",RC6=""Int"",RC6=""""),-1,if(RC6=""NT"",-8,if(RC6=""ACT"",-2,if(RC6=""NZ"",-2,IF(RC6=""WA"",-3,If(RC6=""TAS"",-8,""""))))))"
    .Value = .Value 'comment out to leave formulae present
End With
MsgBox Timer - t
End Sub

Sub Lkup6()
t = Timer
Dim x As Long
For x = 2 To Range("A" & Rows.Count).End(xlUp).Row
    Select Case Range("f" & x).Value
    Case "Vic", "Nsw"
        Range("aa" & x).Formula = "0"
    Case "Nz", "Act"
        Range("aa" & x).Formula = "2"
    Case "Qld", "SA", "Int", """"
        Range("aa" & x).Formula = "-1"
    Case "wA"
        Range("aa" & x).Formula = "-3"
    Case "Tas", "Nt"
        Range("aa" & x).Formula = "-8"
    End Select
Next
MsgBox Timer - t
End Sub

[/TD]
[/TR]
</TBODY>[/TABLE]

 

[Trebor76]

I'm hoping someone can advise why I'm getting such a time difference between two processes doing essentially the same job?

The LKup7 macro is putting the value into the entire range in a single command while the Lkup6 macro is looping through the range a cell at a time then checking five case statements to return the desired output.

HTH

Robert

 

[swallis]

Thanks Robert. Is there no way to replicate the if Function with the Case select statement (or an If Statement)?

steve

 

[Trebor76]

In my humble opinion, filling in a formula (like LKup7 is doing) is the way to go so try this:

Option Explicit
Sub Macro3()

    With Range("AA2:AA" & Cells(Rows.Count, "F").End(xlUp).Row)
        .Formula = "=IF(F2="""",-1,VLOOKUP(F2,{""QLD"",-1;""SA"",-1;""ACT"",-2;""NZ"",-2;""WA"",-3;""NT"",-8;""TAS"",-8},2,0))"
        '.Value = .Value 'comment out to leave formulae present
    End With

End Sub

Regards,

Robert

 

[swallis]

Wow! Easily the quickest. Thanks Robert. Can you explain/confirm a couple of things, just to see if I've got it right? You only stipulate F2 in the formula, but somehow it still looks at each cell in the range? The Lookup is wholly contained in the formula, by means of (is it) an array? Am I able to add states and countries to the array without limit?

Thanks again. I can use this in so many ways

Steve

 

[Trebor76]

You only stipulate F2 in the formula, but somehow it still looks at each cell in the range?

The reference to F2 is for the first cell (AA2) for the formula to be entered which is the filled down the required number of rows in column AA (based on the last row in column F).

The Lookup is wholly contained in the formula, by means of (is it) an array?

No, it's not an array formula but just a standard VLOOKUP formula that I have put the table_array parameter in the formula its as opposed to a range on a worksheet which is typically the case.

Am I able to add states and countries to the array without limit?

Technically there's no limit (cell character limit may cause an issue) but it would be easier to maintain if the range for the VLOOKUP is in worksheet if there are to be many lookups. This would mean changing the macro for the updated formula though.

HTH

Robert

 

[swallis]

I think I follow that. Thanks again Robert. Much Appreciated

steve