CODEWITHSUNDEEP
excelvba
Jav171
Jav171

Reputation: 13

VBA code to insert column and formula

I have a file where I need to delete certain columns. I then need to insert a column called 'positive values' and add a formula so that only the positive values from another column are picked up in this new column.

So far I have pieced together the following code to delete the columns I do not need, but I am stuck at how to insert a new column next to an existing column called "net" and then have this column only show the positive values from column net in the relevant cells.

Current code

Sub ArrayLoop()
  Dim ColumnsToRemove As Variant
  Dim vItem As Variant
  Dim A As Range

  Sheets("sheet 1").Select

  ColumnsToRemove = Array("acronym", "valueusd", "value gbp")
  For Each vItem In ColumnsToRemove
    Set A = Rows(8).Find(What:=vItem, LookIn:=xlValues, lookat:=xlPart)
    Debug.Print vItem, Not A Is Nothing

    If Not A Is Nothing Then A.EntireColumn.Delete
  Next
End Sub

Currently I manually insert the new column and enter the formula max(E9,0) so the new column either shows 0 or a value if the value in the other column is greater than 0. Is it possible to automate this part as well.

Thanks in advance.

Upvotes: 0

Views: 5524

Answers (1)

AcsErno
AcsErno

Reputation: 1625

For Insertion locate the cell and issue: 

If Not A Is Nothing Then A.EntireColumn.Insert

To insert a formula, use cell.formula= with the coresponding string value, e.g.

Cells(1, A.column - 1).Formula = "=max(" & cells(9, A.column - 2).Address & ",0)"

Note A as a range of the found value will shift to the right when inserting a column that's why you need - 1 nad - 2 in cell references.

Upvotes: 1

Related Questions