VBA: Autofill current selected cell down according to adjacent column. Columns will be dynamic.

[CtJester7807]

Hi All,

I've been trying to solve this for a few days now. I am relatively new to VBA, and I am running into trouble due to the data in my worksheets being dynamic.

I need to autofill a formula down to the last row of the adjacent column next to it. The problem I'm running into, is I'm not sure what to title my range, as it wont always be column D for example. I'm aware I can just copy my formula to the bottom of the page, but I would like to keep it cleaner than that. I have been using ActiveCell to drive my steps since I can't rely on any hard coded cells.

What do I need to put in my range, so it selects everything below my active cell down to the last cell of the adjacent column (the column to the left). I also know offset will be used here.

The below is what I have so far.

Cells.Find("Average").Select

Selection.EntireColumn.Offset(0, 1).Insert Shift:=xlToLeft

Selection.End(xlUp).Offset(0, 1).Activate

ActiveCell.Value = "ABS"

ActiveCell.Offset(1, 0).Select

ActiveCell.FormulaR1C1 = "=ABS(RC[-1])"

ActiveCell.Range().FillDown

Thank you!

 

[Fluff]

Hi & welcome to MrExcel.
Will "Average" always be in a particular row?

 

[CtJester7807]

Hello and thank you!

Unfortunately it will not.

 

[CtJester7807]

**My apologies - Yes, Average will always be in the same row. Not the same column.

 

[Fluff]

Ok try this

Sub CtJester()
   Dim RngAv As Range

   Set RngAv = Range("1:1").Find("Average", , , xlWhole, , , False, , False)
   RngAv.Offset(, 1).EntireColumn.Insert
   RngAv.Offset(, 1).Value = "ABS"
   Range(RngAv.Offset(1, 1), Cells(Rows.Count, RngAv.Column).End(xlUp).Offset(, 1)).FormulaR1C1 = "=abs(rc[-1])"
End Sub

This assumes that Average will be in row 1 if not, change the value in red to suit

 

[CtJester7807]

Incredible - you just solved what it took me 2 hours to do incorrectly in a matter of minutes

Thank you SO much. I will now try to make sense of your code!

 

[Fluff]

You're welcome & thanks for the feedback

 

[CtJester7807]

My apologies but I have one more question - While I'm still trying to make sense of how you did this - I realized I need to autosum the new row for which I just pulled the absolute values and put that beneath the last ABS cell. I'm having trouble figuring out how to sum the new data.

 

[Fluff]

How about

Sub CtJester()
   Dim RngAv As Range
   Dim UsdRws As Long

   Set RngAv = Range("1:1").Find("Average", , , xlWhole, , , False, , False)
   UsdRws = Cells(Rows.Count, RngAv.Column).End(xlUp).Row
   RngAv.Offset(, 1).EntireColumn.Insert
   RngAv.Offset(, 1).Value = "ABS"
   With RngAv.Offset(1, 1).Resize(UsdRws - 1)
      .FormulaR1C1 = "=abs(rc[-1])"
      Cells(UsdRws + 1, RngAv.Column + 1).Formula = "=sum(" & .Address & ")"
   End With
End Sub

 

[CtJester7807]

Once again, a big thank you.

No way I was going to get that on my own. Looking forward to be able to contribute elsewhere on this site when I can.