Help with IIF statement in macro

  • Thread starter Thread starter Sam. Commar
  • Start date Start date
S

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