Help with IIF statement in macro
 

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

Help with IIF statement in macro
 

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

.

Help with IIF statement in macro
 

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

"Jacob Skaria" wrote in message
...
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

.

Help with IIF statement in macro
 

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" wrote in message
...
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

.