Solved: Autofill formula across to the last active column

[Shazam]

Hey everyone!

This code below will look for the word "totals" in column A if found it will place the the formula two columns over that coresponds with "totals". Is there a way we could add to the code so that when it place the formula then it will autofill across to the last active column.




[VBA]
Sub Place_Formula()
With ActiveSheet.Columns("A:A")
Set A = .Find("totals", LookIn:=xlValues, lookat:=xlWhole)
If Not A Is Nothing Then
firstAddress = A.Address
Do
A.Offset(0, 2).Formula = "=IF(OR(R[-243]C=""Increase"",R[-243]C=""Decrease"")," & _
"COUNTIF(R[-242]C:R[-1]C,"">0""),IF(R[-243]C=""%"",OFFSET(RC,0,-3,1,1)/OFFSET(RC,0,-4,1,1)," & _
"SUMIF(R[-242]C:R[-1]C,"">0"",R[-242]C:R[-1]C)))"
Set A = .FindNext(A)
Loop While Not A Is Nothing And A.Address <> firstAddress
End If
End With
End Sub

[/VBA]

[Bob Phillips]

Is this what you mean

[vba]
Sub Place_Formula()
Dim oCell As Range
Dim iLastCol As Long

With ActiveSheet.Columns("A:A")
Set oCell = .Find("totals", LookIn:=xlValues, lookat:=xlWhole)
If Not oCell Is Nothing Then
firstAddress = oCell.Address
Do
iLastCol = Cells(oCell.Row, Columns.Count).End(xlToLeft).Column
oCell.Offset(0, 2).Formula = "=IF(OR(R[-243]C=""Increase"",R[-243]C=""Decrease"")," & _
"COUNTIF(R[-242]C:R[-1]C,"">0""),IF(R[-243]C=""%"",OFFSET(RC,0,-3,1,1)/OFFSET(RC,0,-4,1,1)," & _
"SUMIF(R[-242]C:R[-1]C,"">0"",R[-242]C:R[-1]C)))"
oCell.Offset(0, 2).AutoFill oCell.Offset(0, 2).Resize(, iLastCol - 3)
iLastCol = Cells(oCell.Row, Columns.Count).End(xlToLeft).Column
Set oCell = .FindNext(oCell)
Loop While Not oCell Is Nothing And oCell.Address <> firstAddress
End If
End With
End Sub
[/vba]

[Shazam]

Thank You for replying xld I get a error in the code. Its telling me:

Runtime error '1004':
Aplication-defined or object-defined error.

[VBA]
oCell.Offset(0, 2).AutoFill oCell.Offset(0, 2).Resize(, iLastCol - 3)
[/VBA]

I'm most likely overlooking something. I attach the sample workbook below. The worksheet tab named forcast is the one i ran your code. The other tab is the result should look like.

[Bob Phillips]

This might be better

[vba]
Sub Place_Formula()
Dim oCell As Range
Dim iLastCol As Long
Dim firstAddress As String

With ActiveSheet.Columns("A:A")
Set oCell = .Find("totals", LookIn:=xlValues, lookat:=xlWhole)
If Not oCell Is Nothing Then
firstAddress = oCell.Address
Do
iLastCol = ActiveSheet.Cells(2, Columns.Count).End(xlToLeft).Column
oCell.Offset(0, 2).Formula = "=IF(OR(R2C=""Increase"",R2C=""Decrease"")," & _
"COUNTIF(R3C:R[-1]C,"">0""),IF(R2C=""%"",OFFSET(RC,0,-3,1,1)/OFFSET(RC,0,-4,1,1)," & _
"SUMIF(R3C:R[-1]C,"">0"",R3C:R[-1]C)))"
oCell.Offset(0, 2).AutoFill oCell.Offset(0, 2).Resize(, iLastCol - 3)
iLastCol = Cells(oCell.Row, Columns.Count).End(xlToLeft).Column
Set oCell = .FindNext(oCell)
Loop While Not oCell Is Nothing And oCell.Address <> firstAddress
End If
End With
End Sub
[/vba]

[Shazam]

Thank You so much it perfect.

[Shazam]

One more thing xld the code is not inserting the formula for the very last active column.

[Shazam]

I got it I change this line. Thank you xld.


[VBA]
oCell.Offset(0, 2).AutoFill oCell.Offset(0, 2).Resize(, iLastCol - 2)
[/VBA]