Multiple conditions in 2 different columns

Question

I need to loop thru 9 sets of conditions

Here are the 9 sets of Conditions the loop has to run through I have tried If Statements with Select Case. I've tried without Select Case and use ElseIf. I seem to be the most stuck on the writing the structure of the conditions asking the code to look at values in 2 different columns to determine the action.

I'm also struggling on the loop, but if I can get the conditions right, I think I can figure out the looping issue.

Here is the latest version of the code... (incomplete) Do not worry about the action part of the code. this is a sample table of the data I'm working with.

Sub Margin_Five()

Dim i As Integer

'Set i = Range("M7")

'i = i + 1 'Start i off as 1

Dim lastRow As Long 'object creation
    lastRow = Range("I" & Rows.Count).End(xlUp).Row

Dim ws As Worksheet, MyCell As Range
Dim BasedOn As Range

Dim AcctMgr As Range
Dim NewCost As Range
Dim Chgs As Range

    Set AcctMgr = Range("L:L")
    Set NewCost = Range("R:R")
    Set Chgs = Range("F:F")
    Set BasedOn = Range("X:X")

    Set ws = ActiveSheet
    Set Chgs = ws.Range("F:F")
    i = i + 1
    For Each MyCell In (ws.Range("M7:M" & lastRow).Cells

        If Sheets(3).Range("L7") > 0 Then
            ActiveCell.FormulaR1C1 = "=RC[-1]"
                i = i + 1
        ElseIf Sheets(3).Range("F7") = "Removed" And Sheets(3).Range("X7") = "L" Then
            ActiveCell.FormulaR1C1 = "=(1-(RC[5]/RC[12]))*100"
                 = i + 1
        ElseIf Sheets(3).Range("F7") = "Removed" And Sheets(3).Range("X7") Then
            ActiveCell.FormulaR1C1 = "=(1-(RC[5]/(RC[7]/(1-RC[10]/100))))*100"
                i = i + 1
        ElseIf Sheets(3).Range("F7") = "Removed" And Sheets(3).Range("X7") Then
            ActiveCell.FormulaR1C1 = "=(1-(RC[5]/(RC[7]/(1-RC[10]/100))))*100"
                i = i + 1
        ElseIf Sheets(3).Range("F7") = "Removed" And Sheets(3).Range("X7") Then
            ActiveCell.FormulaR1C1 = "N/A"
                i = i + 1
        End If
    Next MyCell

End Sub

Answer 1

You can build a dictionary of the 8 formula strings and use exists to select the appropriate one.

Sub Margin_Five()

    Dim ws As Worksheet, lastrow As Long, i As Long
    Dim dict As Object, key As String
    Set dict = CreateObject("Scripting.Dictionary")
    ' margin calc
    With dict
        .Add "Removed-L", "=(1-(RC[5]/RC[12]))*100"
        .Add "Removed-M", "=(1-(RC[5]/(RC[7]/(1-RC[10]/100))))*100"
        .Add "Removed-R", "=(1-(RC[5]/(RC[7]/(1-RC[10]/100))))*100"
        .Add "Removed-B", "N/A"
        .Add "Cost Chg-L", "=(1-(RC[5]/RC[12]))*100"
        .Add "Cost Chg-M", "=(1-(RC[5]/(RC[7]/(1-RC[10]/100))))*100"
        .Add "Cost Chg-R", "=(1-(RC[5]/(RC[7]/(1-RC[10]/100))))*100"
        .Add "Cost Chg-B", "N/A"
    End With
   
    Set ws = ActiveSheet
    With ws
        lastrow = .Range("I" & .Rows.Count).End(xlUp).Row
        For i = 7 To lastrow
            ' combine2 conditions in key
            key = .Cells(i, "F") & "-" & .Cells(i, "X")
            If .Cells(i, "L") > 0 Then ' sales pref
                ActiveCell.FormulaR1C1 = "=RC[-1]"
            ElseIf dict.exists(key) Then ' other 8 options
                ActiveCell.FormulaR1C1 = dict(key)
            End If
        Next
    End With

End Sub