Help with IIF statement in macro

[Sam. Commar]

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

 

[Jacob Skaria]

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"))

 

[Sam. Commar]

Could you give me the exact syntqax I could use as I am not getting it right
Thanks

 

[Jacob Skaria]

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