CODEWITHSUNDEEP
excelvba
Bluesector
Bluesector

Reputation: 329

Use Round function within VBA function

This function returns multiple lookup matches within one cell: 

Function NBlookup(lookupval, lookuprange As Range, indexcol As Long)
Dim result As String
Dim x As Range

result = ""
For Each x In lookuprange
    If x = lookupval Then
        result = result & " " & x.Offset(0, indexcol - 1) & x.Offset(0, 5)
    End If
Next x
NBlookup = result
End Function

The following part concatenates it with a number value:

& x.Offset(0, 5)

I want to roundup the value: if & x.Offset(0, 5) >= 5 then roundup, otherwise round down.

How can I implement this?

Upvotes: 1

Views: 127

Answers (2)

R3uK
R3uK

Reputation: 14537

Just use Round() function, like this : Round(x.Offset(0, 5), 0)

After re-reading your post, it seems that you are trying to round up or down, around 5 and not 0.5
If that's the case, I'll do some thing like this : Round(x.Offset(0, 5)/10, 0)*10

Public Function NBlookup(lookUpVal, lookUpRange As Range, indexCol As Long)
    Dim Result As String
    Dim x As Range

    Result = ""
    For Each x In lookUpRange
        If x = lookUpVal Then
            Result = Result & " " & x.Offset(0, indexCol - 1) & Round(x.Offset(0, 5), 0)
        End If
    Next x
    NBlookup = Result
End Function

I've added a few Caps in your variables' names (lower camel case), to see a bit much what is what.

I've also declare your function as Public so that you can use it in Excel cells directly. Anyhow it is a good practice to declare the scope (Public, Private, Friend) of your functions or subs. ;)

Upvotes: 3

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11978

Try replacing & x.Offset(0, 5) with

& Application.WorksheetFunction.Round(x.Offset(0, 5),0)

Upvotes: 1

Related Questions