CODEWITHSUNDEEP
excelvbaexcel-formula
SAM HO
SAM HO

Reputation: 15

complex VBA function with excel IF, index, match function

Please help me to create VBA function wtih excel IF, index, match function. here is my excel function :

=IF(INDEX(Grid!$B$3:$G$6,MATCH('Sal-Data'!$A2,Grid!$A$3:$A$6,0),MATCH('Sal-Data'!$B2+1,Grid!$B$2:$G$2,0))=0,$B2,$B2+1)

I have added VBA codes here ,which is not working, I want a result for next Level from Grid Sheet. example if current Grid in Sheet"Sal-data" for Emp "b" is 2 then new Grid would be 3, but value in "Grid" sheet for "b" Grid 3 is 0 (zero) then Grid should not be increase. In this case if "b" current grid is 3 then b's next grid may get 4 , but grid 4 value is 0 then "b"s grid should not increase as there is no value.

Here is my VBA coding :

Public Function NGrid(rData As Range, rBudCode As Range, rMo As Range, budgetcode As Variant, mo As Variant)
Dim rw As Variant
Dim col As Variant
    With Application
        col = .Match(budgetcode+1, rBudCode, 0)
        rw = .Match(mo, rMo, 0)
        NGrid = .Index(rData, col, rw)

If NGrid= 0 Then
 With Application
col = .Match(budgetcode, rBudCode, 0)
        rw = .Match(mo, rMo, 0)
        NGrid = .Index(rData, col, rw)
Else
With Application
col = .Match(budgetcode+1, rBudCode, 0)
        rw = .Match(mo, rMo, 0)
        NGrid = .Index(rData, col, rw)
End if 
End with 
End Function

 Excel sheet name : Grid

    1   2   3   4   5   6
a   10  11  12  13  0   0
b   20  21  22  0   0   0
c   30  31  32  33  34  0
d   40  41  42  0   0   0


Excel sheet name : Sal-Data

Emp grid    New grid
a   4   = NGrid (...) required VBA function
a   2   
b   1   
b   2

Than you in advance.

Upvotes: 0

Views: 852

Answers (2)

Scott Craner
Scott Craner

Reputation: 152450

One, you do not need the Else as it just repeats the first.

Two, every With needs an End With, in this case the other With Application are not needed.

Three, Index is Row,Column not Column,Row

Public Function NGrid(rData As Range, rBudCode As Range, rMo As Range, budgetcode As Variant, mo As Variant)
Dim rw As Long
Dim col As Long

With Application
    col = .Match(budgetcode + 1, rBudCode, 0)
    rw = .Match(mo, rMo, 0)
    NGrid = .Index(rData, rw, col)
    If NGrid = 0 Then
        col = .Match(budgetcode, rBudCode, 0)
        rw = .Match(mo, rMo, 0)
        NGrid = .Index(rData, rw, col)
    End If
End With
End Function

Then make sure you include the title row and column in rData:

=NGrid(Grid!$A$1:$G$5,Grid!$A$1:$G$1,Grid!$A$1:$A$5,B2,A2)

enter image description here

This allows the use of just the range of the grid, no need to specify the first row and column differently:

Public Function NGrid(rData As Range, budgetcode As Variant, mo As Variant)
Dim rw As Long
Dim col As Long
Dim rBudCode As Range
Dim rMo As Range
With Application
    Set rBudCode = rData.Rows(1)
    Set rMo = rData.Columns(1)
    col = .Match(budgetcode + 1, rBudCode, 0)
    rw = .Match(mo, rMo, 0)
    NGrid = .Index(rData, rw, col)
    If NGrid = 0 Then
        NGrid = .Index(rData, rw, col - 1)
    End If
End With
End Function

So the above formula would be:

=NGrid(Grid!$A$1:$G$5,B2,A2)

enter image description here

Grid sheet: for reference

enter image description here

Upvotes: 0

Tim Williams
Tim Williams

Reputation: 166126

Untested:

Public Function NGrid(rData As Range, rBudCode As Range, rMo As Range, budgetcode As Variant, mo As Variant)
    Dim rw As Variant, col As Variant

    With Application
        col = .Match(budgetcode + 1, rBudCode, 0)
        rw = .Match(mo, rMo, 0)

        If Not IsError(rw) Then
            If Not IsError(col) Then
                NGrid = .Index(rData, col, rw)
                If NGrid = 0 Then
                    col = .Match(budgetcode, rBudCode, 0)
                    If Not IsError(col) Then
                        NGrid = .Index(rData, col, rw)
                    Else
                        NGrid = "budget?" 'no col match
                    End If
                End If
            Else
                NGrid = "budget+1?" 'no col match on +1
            End If
        Else
            NGrid = "mo?" 'no row match
        End If
    End With

End Function

If your source data always has the lookups in the first column and row then you really only need to pass in a single range: you can pick out the lookup ranges from that using .Rows(1) and .Columns(1)

Upvotes: 0

Related Questions