Help with IIF statement in macro
in [Programming]
|
Prev: Macro for hiding all rows accept those selected by a dated column?
Next: referencing controls in userform from a different workbook
From: Sam. Commar on 28 May 2010 01:02 I am using the followign statement in my macro: Sheets("Split").Range("K" & LoopID).Value = IIf(Val(Sheets("New York").Range("I" & StartPoint) & "") < 4, "DATABASE", "FURNITURE") So I am saying if value in Range I < 4 then its DATABASE else its FURNITURE I want to add one more parameter.. that is if I < 4 then its Database if its between 4 to 7 then its Furniture and if its more than 7 then its Leasehold. Could someone please advise me on what the statement should be modified to. Thanks S Commar
From: Jacob Skaria on 28 May 2010 02:03 You could either use IF..ElseIF..Else OR use Select Case statement... OR you can use the the WorksheetFunction LOOKUP() as below Dim varLookup As Variant varLookup = Val("0" & Sheets("New York").Range("I" & StartPoint)) Sheets("Split").Range("K" & LoopID).Value = WorksheetFunction.Lookup _ (varLookup, Array(0, 4, 8), Array("Database", "Furniture", "Leasehold")) -- Jacob (MVP - Excel) "Sam. Commar" wrote: > I am using the followign statement in my macro: > > Sheets("Split").Range("K" & LoopID).Value = IIf(Val(Sheets("New > York").Range("I" & StartPoint) & "") < 4, "DATABASE", "FURNITURE") > > So I am saying if value in Range I < 4 then its DATABASE else its FURNITURE > > I want to add one more parameter.. that is if I < 4 then its Database if its > between 4 to 7 then its Furniture and if its more than 7 then its > Leasehold. > > Could someone please advise me on what the statement should be modified to. > > Thanks > > S Commar > > . >
From: Sam. Commar on 28 May 2010 04:36 Could you give me the exact syntqax I could use as I am not getting it right Thanks "Jacob Skaria" <JacobSkaria(a)discussions.microsoft.com> wrote in message news:A37E3D63-7112-48C0-B275-876EA97FB096(a)microsoft.com... > You could either use IF..ElseIF..Else OR use Select Case statement... > > OR you can use the the WorksheetFunction LOOKUP() as below > > Dim varLookup As Variant > varLookup = Val("0" & Sheets("New York").Range("I" & StartPoint)) > Sheets("Split").Range("K" & LoopID).Value = WorksheetFunction.Lookup _ > (varLookup, Array(0, 4, 8), Array("Database", "Furniture", "Leasehold")) > > > -- > Jacob (MVP - Excel) > > > "Sam. Commar" wrote: > >> I am using the followign statement in my macro: >> >> Sheets("Split").Range("K" & LoopID).Value = IIf(Val(Sheets("New >> York").Range("I" & StartPoint) & "") < 4, "DATABASE", "FURNITURE") >> >> So I am saying if value in Range I < 4 then its DATABASE else its >> FURNITURE >> >> I want to add one more parameter.. that is if I < 4 then its Database if >> its >> between 4 to 7 then its Furniture and if its more than 7 then its >> Leasehold. >> >> Could someone please advise me on what the statement should be modified >> to. >> >> Thanks >> >> S Commar >> >> . >>
From: Jacob Skaria on 28 May 2010 05:51
Have you tried the LOOKUP() code..Try the others Sub Macro1() Dim strResult As String Select Case Val("0" & Sheets("New York").Range("I" & StartPoint)) Case Is < 4 strResult = "Database" Case Is < 8 strResult = "Furniture" Case Else strResult = "Leasehold" End Select Sheets("Split").Range("K" & LoopID).Value = strResult End Sub Sub Macro2() Dim strResult As String, varValue As Variant varValue = Val("0" & Sheets("New York").Range("I" & StartPoint)) If varValue < 4 Then strResult = "Database" ElseIf varValue < 8 Then strResult = "Furniture" Else strResult = "Leasehold" End If Sheets("Split").Range("K" & LoopID).Value = strResult End Sub -- Jacob (MVP - Excel) "Sam. Commar" wrote: > Could you give me the exact syntqax I could use as I am not getting it right > Thanks > > "Jacob Skaria" <JacobSkaria(a)discussions.microsoft.com> wrote in message > news:A37E3D63-7112-48C0-B275-876EA97FB096(a)microsoft.com... > > You could either use IF..ElseIF..Else OR use Select Case statement... > > > > OR you can use the the WorksheetFunction LOOKUP() as below > > > > Dim varLookup As Variant > > varLookup = Val("0" & Sheets("New York").Range("I" & StartPoint)) > > Sheets("Split").Range("K" & LoopID).Value = WorksheetFunction.Lookup _ > > (varLookup, Array(0, 4, 8), Array("Database", "Furniture", "Leasehold")) > > > > > > -- > > Jacob (MVP - Excel) > > > > > > "Sam. Commar" wrote: > > > >> I am using the followign statement in my macro: > >> > >> Sheets("Split").Range("K" & LoopID).Value = IIf(Val(Sheets("New > >> York").Range("I" & StartPoint) & "") < 4, "DATABASE", "FURNITURE") > >> > >> So I am saying if value in Range I < 4 then its DATABASE else its > >> FURNITURE > >> > >> I want to add one more parameter.. that is if I < 4 then its Database if > >> its > >> between 4 to 7 then its Furniture and if its more than 7 then its > >> Leasehold. > >> > >> Could someone please advise me on what the statement should be modified > >> to. > >> > >> Thanks > >> > >> S Commar > >> > >> . > >> |