Formula/Macro

[praf007]

We download reports from a software into Excel. All Values are debits (+ve) in Col P (Could be any column depending on report). Col Q has Debit and Credit. Normally I insert a Col and create a formula -=if(Q2="Debit",P2,-P2)
How can I create a macro which does this automatically. Number of rows can vary along with which Columns have values and Debits/Credits

 

[ranman256]

paste code into a module , then run to insert formulas

Sub buildFormula()
Dim iRows As Long

Range("A1").Select
iRows = ActiveSheet.UsedRange.Rows.Count
    
'add col.
    Columns("R:R").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
   
    
Range("R1").Value = "formula"
Range("R2").Select
While ActiveCell.Row <= iRows
    ActiveCell.FormulaR1C1 = "=IF(RC[-1]=""Debit"",RC[-2],-RC[-2])"
    ActiveCell.Offset(1, 0).Select
Wend
MsgBox "done"
End Sub

 

[kvsrinivasamurthy]

Try

Sub GetValue()
Dim LR&
LR = Range("Q" & Rows.Count).End(xlUp).Row
Range("R2:R" & LR).Insert shift:=xlToRight
Range("R2:R" & LR).Formula = "=If(Q2=""Debit"",-1*P2,P2)"
End Sub

 

[praf007]

paste code into a module , then run to insert formulas

Sub buildFormula()
Dim iRows As Long

Range("A1").Select
iRows = ActiveSheet.UsedRange.Rows.Count
   
'add col.
    Columns("R:R").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
  
   
Range("R1").Value = "formula"
Range("R2").Select
While ActiveCell.Row <= iRows
    ActiveCell.FormulaR1C1 = "=IF(RC[-1]=""Debit"",RC[-2],-RC[-2])"
    ActiveCell.Offset(1, 0).Select
Wend
MsgBox "done"
End Sub

Thanks Ranman. Tried it a few times on different report and it works. Just need to be aware that i need to change Add Columns From R to what ever column.

 

[praf007]

Try

Sub GetValue()
Dim LR&
LR = Range("Q" & Rows.Count).End(xlUp).Row
Range("R2:R" & LR).Insert shift:=xlToRight
Range("R2:R" & LR).Formula = "=If(Q2=""Debit"",-1*P2,P2)"
End Sub

Will try this as well. thank you

 

[Joe4]

Will try this as well. thank you

The second code should perform much better for 2 reasons:

1. It does not use loops. Loops are notoriously slow and should be avoided whenever possible. When there are other better options available, you should choose them instead of loops.

2. It does not use "Select" statements. "Select" statement slow down your code and can cause screen flickering. Most of the time, it is not necessary to select your ranges in order to work with them. If you use the Macro Recorder, it records every cell selection literally, so there is often an opportunity to clean up VBA code that is recorded, and make it shorter and run smoother.