PDA

View Full Version : Solved: Autofill formula across to the last active column



Shazam
02-21-2006, 05:46 AM
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.





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

xld
02-21-2006, 05:58 AM
Is this what you mean


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

Shazam
02-21-2006, 06:59 AM
Thank You for replying xld I get a error in the code. Its telling me:

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


oCell.Offset(0, 2).AutoFill oCell.Offset(0, 2).Resize(, iLastCol - 3)


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.

xld
02-21-2006, 07:46 AM
This might be better


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

Shazam
02-21-2006, 07:54 AM
Thank You so much it perfect.

Shazam
02-21-2006, 08:27 AM
One more thing xld the code is not inserting the formula for the very last active column.

Shazam
02-21-2006, 09:41 AM
I got it I change this line. Thank you xld.



oCell.Offset(0, 2).AutoFill oCell.Offset(0, 2).Resize(, iLastCol - 2)