IF Statements; clarification on approach

[NinaE_11]

Hi,
I'm looking for help on how to approach programming; not sure which VBA method I should be researching get outcome I am looking for. Scenario is that I will have a spreadsheet with multiple rows of buy/sell trade details for portfolio management. Unfortunately our in-house reporting software can't generate report I want, so I'm pushing it out to Excel for client needs. Some trade details will be automation, but others I will need to back into. I can get gross amount, but if the trade is a buy, I will have to back out commission, and if trade is a sell, I will have to add commission.

So if cell A1 is a 'by' transaction, I will need to take gross amount (B1) and subtract commission (C1). Conversely, If A1 is a 'sl' transaction the formula will be =B1+C1. There will also be other simple math to do, but it will all be based on what side trade is on, so it needs to start; IF this, then that... Would this be a loop of some kind, or is a better approach to do the FunctionIfCellContainsText ? I'm not confident in either alternative, but any guidance on where to start would be helpful!

Thank you!
Nina

 

[Joe4]

It sounds like maybe you just need a formula like this:

=IF(A1="by",B1-C1,IF(A1="sl",B1+C1,""))

 

[NinaE_11]

It sounds like maybe you just need a formula like this:

=IF(A1="by",B1-C1,IF(A1="sl",B1+C1,""))

Thank you - how does this convert to VBA language?

 

[Joe4]

Why do you need it in VBA?
You should just be able to put the formula on your sheet and copy it down for as many rows as you need.
You can have VBA enter and copy the formula down, i.e. this will look at cell A1 to find the last row and copy the formula down that many rows.

Sub MyMacro()

    Dim lr As Long
   
'   Find last row with data in column A
    lr = Cells(Rows.Count, "A").End(xlUp).Row

'   Populate all the cells in column D with formula
    Range("D1:D" & lr).FormulaR1C1 = _
        "=IF(RC[-3]=""by"",RC[-2]-RC[-1],IF(RC[-3]=""sl"",RC[-2]+RC[-1],""""))"
       
End Sub

If you want to know how I got the relative formula to look like that, simply turn on your Macro Recorder and record yourself entering the formula in the first cell (D1).
Then stop the Macro Recorder and view the code you just recorded. This is a great tool to get VBA code snippets (like Excel do the heavy lifting!).

 

[rlv01]

Another variation on the formula refresh theme.

'Assumes formula will be in Col D
Sub AdjGrossFormulaRefresh()
    Dim FormulaStr As String
    
    With Range("A1", Range("A" & Rows.Count).End(xlUp)).Offset(0, 3) 'Offset 3 = Col D
        'Formula
        FormulaStr = "=IF(A1=""by"",B1-C1,IF(A1=""sl"",B1+C1,""n/a""))"
        
        'adj starting row of formula
        FormulaStr = Replace(FormulaStr, "1", .Cells(1, 1).Row)
        
        .Formula = FormulaStr
    End With
End Sub

 

[NinaE_11]

Why do you need it in VBA?
You should just be able to put the formula on your sheet and copy it down for as many rows as you need.
You can have VBA enter and copy the formula down, i.e. this will look at cell A1 to find the last row and copy the formula down that many rows.

Sub MyMacro()

    Dim lr As Long
  
'   Find last row with data in column A
    lr = Cells(Rows.Count, "A").End(xlUp).Row

'   Populate all the cells in column D with formula
    Range("D1:D" & lr).FormulaR1C1 = _
        "=IF(RC[-3]=""by"",RC[-2]-RC[-1],IF(RC[-3]=""sl"",RC[-2]+RC[-1],""""))"
      
End Sub

If you want to know how I got the relative formula to look like that, simply turn on your Macro Recorder and record yourself entering the formula in the first cell (D1).
Then stop the Macro Recorder and view the code you just recorded. This is a great tool to get VBA code snippets (like Excel do the heavy lifting!).

Thank you very much! This has been tested and works as expected. I appreciate your follow up and guidance towards more macro building with this feature, too!

 

[Joe4]

You are welcome.
Glad we were able to help!