Can I use VBA to return value based on multiple variables?

[Arinoum]

Below are the conditions and the desired return value if conditions are met:

My table looks like
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Status[/TD]
[TD]Type[/TD]
[TD]GM$[/TD]
[TD]GM%[/TD]
[TD]Dues[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Here are the conditions. These conditions only apply if GM$ & GM% are above $25 or 25%. If GM% & GM$ aren't above = or more than 25 the cell should return $0 in dues.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Status[/TD]
[TD]Type[/TD]
[TD]Dues[/TD]
[/TR]
[TR]
[TD]Pending[/TD]
[TD]Emp[/TD]
[TD]4000[/TD]
[/TR]
[TR]
[TD]Term<30[/TD]
[TD]Emp[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Term<60[/TD]
[TD]Emp[/TD]
[TD]1333[/TD]
[/TR]
[TR]
[TD]Term<90[/TD]
[TD]Emp[/TD]
[TD]2000[/TD]
[/TR]
[TR]
[TD]Pending[/TD]
[TD]C2C[/TD]
[TD]1500[/TD]
[/TR]
[TR]
[TD]Term<30[/TD]
[TD]C2C[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Term<60[/TD]
[TD]C2C[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]Term<90[/TD]
[TD]C2C[/TD]
[TD]1000[/TD]
[/TR]
</tbody>[/TABLE]


Can I use VBA to assess whether these conditions are met and return the desired value? Example: assuming GM$ & GM% are met at 25 then if status is = Term<60 and Type = Emp then I would like the cell to return $1,333 value. How can I make this possible?
Please help with formula. Thank you!

 

[shknbk2]

Without seeing your data, it's hard to know if this will work. However, it worked on some test data I generated.

Put this code in a vba module and run it:

Sub duesCalculation()
    Dim i As Long, duesValue As Integer
    Dim sh1 As Worksheet, sh2 As Worksheet
    
    Set sh1 = ActiveSheet
    Set sh2 = Worksheets("Sheet2")
    
    For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
        duesValue = 0
        If Cells(i, 3).Value >= 25 And Cells(i, 4).Value >= 25 Then
            Dim searchStr1 As String, searchStr2 As String
            searchStr1 = Cells(i, 1).Value
            searchStr2 = Cells(i, 2).Value
            
            sh2.Activate
            Dim rgSearch As Range
            Set rgSearch = sh2.Columns(1)
        
            Dim cell As Range
            Set cell = rgSearch.Find(searchStr1)
        
            If Not cell Is Nothing Then
                Dim firstCellAddress As String
                firstCellAddress = cell.Address
            
                Do
                    If Range(firstCellAddress).Offset(0, 1).Value = searchStr2 Then
                        duesValue = Range(firstCellAddress).Offset(0, 2).Value
                        Exit Do
                    End If
                    Set cell = rgSearch.FindNext(cell)
                Loop While firstCellAddress <> cell.Address
            End If
        End If
        sh1.Activate
        Cells(i, 5).Value = duesValue
    Next
End Sub

 

[Arinoum]

Without seeing your data, it's hard to know if this will work. However, it worked on some test data I generated.

Put this code in a vba module and run it:

Sub duesCalculation()
    Dim i As Long, duesValue As Integer
    Dim sh1 As Worksheet, sh2 As Worksheet
    
    Set sh1 = ActiveSheet
    Set sh2 = Worksheets("Sheet2")
    
    For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
        duesValue = 0
        If Cells(i, 3).Value >= 25 And Cells(i, 4).Value >= 25 Then
            Dim searchStr1 As String, searchStr2 As String
            searchStr1 = Cells(i, 1).Value
            searchStr2 = Cells(i, 2).Value
            
            sh2.Activate
            Dim rgSearch As Range
            Set rgSearch = sh2.Columns(1)
        
            Dim cell As Range
            Set cell = rgSearch.Find(searchStr1)
        
            If Not cell Is Nothing Then
                Dim firstCellAddress As String
                firstCellAddress = cell.Address
            
                Do
                    If Range(firstCellAddress).Offset(0, 1).Value = searchStr2 Then
                        duesValue = Range(firstCellAddress).Offset(0, 2).Value
                        Exit Do
                    End If
                    Set cell = rgSearch.FindNext(cell)
                Loop While firstCellAddress <> cell.Address
            End If
        End If
        sh1.Activate
        Cells(i, 5).Value = duesValue
    Next
End Sub

Here's what it looks like(image in below link). I highlighted the cells that have the variables in yellow and the green one is where the desired return value should go. The table in the bottom is actually on the 2nd tab sheet1. I would like the formula to read cross the row ex:A8:J8 and check if conditionas are met per the table in sheet 1. If Status says pending and type says Emp and both GM$ and GM% are above $25 then return value in Tab 2 C6. If Status says Term<60 and type says C2C (and GM$ & GM% are above 25) then return value in tab 2 cell C11. I hope that makes sense.


Thanks in advance!

https://imgur.com/RPixrFb

 

[shknbk2]

Same code with adjustments to where the data should be looking.

Sub duesCalculation()
    Dim i As Long, duesValue As Integer
    Dim sh1 As Worksheet, sh2 As Worksheet
    
    Set sh1 = Worksheets("Sheet1")  'change this to the worksheet name with the Status,Client,GM table
    Set sh2 = Worksheets("Sheet2")  'change this to the worksheet name with the status terms
    
    For i = 8 To Cells(Rows.Count, 1).End(xlUp).Row
        duesValue = 0
        If Cells(i, 9).Value >= 25 And Cells(i, 10).Value >= 25 Then
            Dim searchStr1 As String, searchStr2 As String
            searchStr1 = Cells(i, 1).Value
            searchStr2 = Cells(i, 6).Value
            
            sh2.Activate
            Dim rgSearch As Range
            Set rgSearch = sh2.Columns(1)
        
            Dim cell As Range
            Set cell = rgSearch.Find(searchStr1)
        
            If Not cell Is Nothing Then
                Dim firstCellAddress As String
                firstCellAddress = cell.Address
            
                Do
                    If Range(firstCellAddress).Offset(0, 1).Value = searchStr2 Then
                        duesValue = Range(firstCellAddress).Offset(0, 2).Value
                        Exit Do
                    End If
                    Set cell = rgSearch.FindNext(cell)
                Loop While firstCellAddress <> cell.Address
            End If
        End If
        sh1.Activate
        Cells(i, 11).Value = duesValue
    Next
End Sub

Also note that in the image you spelled Pending wrong in the first table. No results would come back with that.

P.S. If the number in the GM% is actually a percentage, then it will be below 1, not above 25. If so, change the code to check for the value to be greater than or equal to 0.25 (e.g., Cells(i, 10).Value >= 0.25).