VBA inquiry to create columns and run formulas

[rn119]

I need to create two new columns that are right adjacent to a specific column which I'll call "Charged Amount". The two new columns will have two unique headers created....one will be called "Discrepancy - Amount" while the other will be called "Discrepancy - Percentage". Once that's done I need to run some basic formulas on those two new columns based on the presence of the "Charged Amount" values and another column (we'll call this "Inventory Amount"). I can get this to work with the code below if the "Charged Amount" column (Column Q in this case) and the "Inventory Amount" column (Column P here) are static.

Private Sub CommandButton1_Click()


Dim ChargedAmount As Range
lastRow = Range("A" & Rows.Count).End(xlUp).Row
Set ChargedAmount = Range("A1:Z1").Find("Charged Amount")
If ChargedAmount Is Nothing Then
MsgBox "Charged Amount column was not found."
Exit Sub
Else
Columns(ChargedAmt.Column).Offset(, 1).Resize(, 2).Insert
Range("R1").Select
ActiveCell.FormulaR1C1 = "Discrepancy - Amount"
Range("S1").Select
ActiveCell.FormulaR1C1 = "Discrepancy - Percentage"
Range("R2").Formula = "=(Q2-P2)"
Range("R2").Select
Selection.NumberFormat = "$#,##0.00"
Range("S2").Formula = "=(Q2/P2-1)"
Range("S2").Select
Selection.NumberFormat = "0.00%"
Range("R2").AutoFill Destination:=Range("R2:R" & lastRow)
Range("S2").AutoFill Destination:=Range("S2:S" & lastRow)


End If




End Sub

But I need something that's a bit more dynamic if the columns were to shift - basically keying in off just the names. Any help with this?

 

[Joe4]

Give this a try:

Private Sub CommandButton1_Click()

    Dim ChargedAmount As Range
    Dim lastRow As Long
    Dim cac As Long

    lastRow = Range("A" & Rows.Count).End(xlUp).Row

    Set ChargedAmount = Range("A1:Z1").Find("Charged Amount")
    
    If ChargedAmount Is Nothing Then
        MsgBox "Charged Amount column was not found."
        Exit Sub
    Else
        cac = ChargedAmount.Column
        Columns(cac).Offset(, 1).Resize(, 2).Insert
        Cells(1, cac + 1) = "Discrepancy - Amount"
        Cells(1, cac + 2) = "Discrepancy - Percentage"
        Range(Cells(2, cac + 1), Cells(lastRow, cac + 1)).FormulaR1C1 = "=RC[-1]-RC[-2]"
        Range(Cells(2, cac + 1), Cells(lastRow, cac + 1)).NumberFormat = "$#,##0.00"
        Range(Cells(2, cac + 2), Cells(lastRow, cac + 2)).Formula = "=(RC[-2]/RC[-3]-1)"
        Range(Cells(2, cac + 2), Cells(lastRow, cac + 2)).NumberFormat = "0.00%"
    End If

End Sub

 

[rn119]

This is great Joe4. Just one other question. If there were columns shifted between the "Inventory Amount" and the "Charged Amount", is there a way to still dynamically calculate the formulas solely based on the column headers i.e., just the Charged Amount and the Inventory Amount?

 

[Joe4]

is there a way to still dynamically calculate the formulas solely based on the column headers i.e., just the Charged Amount and the Inventory Amount?

This part of the code section I posted about dynamically finds where the "Charged Amount" header occurs is row 1, and capture that column number in the "cac" variable:

    Set ChargedAmount = Range("A1:Z1").Find("Charged Amount")
    
    If ChargedAmount Is Nothing Then
        MsgBox "Charged Amount column was not found."
        Exit Sub
    Else
        cac = ChargedAmount.Column

You can use the same logic to find the column position of ANY header title.