VBA Name New column & Use Column Header as Range

[th259]

'Insert Column to the Right of Encumbered Column
Rows(1).Find("Encumbered").Offset(, 1).EntireColumn.Insert
Columns("V:V").Select
    Range("V1").Select
    ActiveCell.FormulaR1C1 = "Total"
    Dim LR As Long 'LR = Last row
    LR = Cells(Rows.Count, "A").End(xlUp).Row 'Finds last row in column A
    Range("V2").FormulaR1C1 = "=RC[1]+RC[2]-RC[74]"
    Range("V2:V" & LR).FillDown 'Copy the formula to the last row
    Range(Selection, Selection.End(xlDown)).Select
    Columns("V:V").EntireColumn.AutoFit

I use the above to insert a new column to the left of column Encumbered, which happens to be column V, how do I immediate insert a header titled "Total" to the first row and how do I use that header in a formula.

 

[Peter_SSs]

When posting vba code in the forum, please use the available code tags, not quote tags. My signature block below has more details. I have swapped the tags for you this time.

I use the above to insert a new column to the left of column Encumbered

I think you mean to the right?

how do I immediate insert a header titled "Total" to the first row

You can insert the column & heading like this

With Rows(1).Find("Encumbered")
  .Offset(, 1).EntireColumn.Insert
  .Offset(, 1).Value = "Total"
End With

how do I use that header in a formula.

You will need to explain that further.
Are you talking about the formula(s) being inserted into that new column?
What exactly is the formula supposed to do?

 

[th259]

I changed the order a little bit, but the newly inserted Total Column is column X, the 3 columns to the right are titled, Actual, DR, and CR, respectively. I wanted to insert a simple formula, adding the numbers in the Actual and DR column then subtracting the number in the CR column. Below is what I currently have but I want to change it so that the range reference the column header instead of X2.

With Rows(1).Find("Encumbered")
.Offset(, 1).EntireColumn.Insert
.Offset(, 1).Value = "Total"
End With
Dim LR As Long 'LR = Last row
LR = Cells(Rows.Count, "A").End(xlUp).Row 'Finds last row in column A
Range("X2").FormulaR1C1 = "=RC[1]+RC[2]-RC[3]"
Range("X2:X" & LR).FillDown 'Copy the formula to the last row
Range(Selection, Selection.End(xlDown)).Select
Columns("X:X").EntireColumn.AutoFit

 

[Peter_SSs]

Below is what I currently have ..

Could you review the first paragraph in my previous post and provide the code again with the code tags?

 

[th259]

I'm sorry, I missed the first sentence.

With Rows(1).Find("Encumbered")
.Offset(, 1).EntireColumn.Insert
.Offset(, 1).Value = "Total"
End With
Dim LR As Long 'LR = Last row
LR = Cells(Rows.Count, "A").End(xlUp).Row 'Finds last row in column A
Range("X2").FormulaR1C1 = "=RC[1]+RC[2]-RC[3]"
Range("X2:X" & LR).FillDown 'Copy the formula to the last row
Range(Selection, Selection.End(xlDown)).Select
Columns("X:X").EntireColumn.AutoFit

 

[Peter_SSs]

Thanks, but next time copy the code from your vba window, not the post in the forum. From the vba window the various indentations would have been preserved whereas they were lost in the quote tags & I think that you have copied from there.

Give this a try with a copy of your workbook.

Sub Test()
  Dim LR As Long 'LR = Last row
  
  LR = Cells(Rows.Count, "A").End(xlUp).Row 'Finds last row in column A
  
  With Rows(1).Find("Encumbered")     'Find 'Encumbered' in row 1
    .Offset(, 1).EntireColumn.Insert  'Insert Column to the Right of Encumbered
    With .Offset(, 1)
      .Resize(LR).FormulaR1C1 = "=RC[1]+RC[2]-RC[3]"  'Insert formulas in new column
      .Value = "Total"                                'Add heading
      .EntireColumn.AutoFit                           'Autofit column width
    End With
  End With
End Sub

 

[th259]

Thank you very much!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.