Eurekaonide
Active Member
- Joined
- Feb 1, 2010
- Messages
- 422
Hi
I'm trying to use an Input box to obtain a variable which then relates to a column in each worksheet in order to then filter on all the Actuals and paste a formula all the way down. This needs to lop through each worksheet. I have the below but it errors on with block or object.
Thank you for your help, its driving me crazy !
ActiveSheet.Range("2:2").AutoFilter Field:=2, Criteria1:="Actual"
and insert the this formula and copy it down on all the filtered cells. This also needs to loop through each worksheet.
ActiveCell.FormulaR1C1 = "=SUMIFS(Actuals!C9,Actuals!C4,R1C1,Actuals!C8,RC1)"
Selection.NumberFormat = "_-* #,##0.00_-;-* #,##0.00_-;_-* ""-""_-;_-@_-"
Selection.Copy
Range(Selection, Selection.End(xlDown).Offset(-1, 0)).Select
ActiveSheet.Paste
I keep getting the object or with block error again.
I'm trying to use an Input box to obtain a variable which then relates to a column in each worksheet in order to then filter on all the Actuals and paste a formula all the way down. This needs to lop through each worksheet. I have the below but it errors on with block or object.
VBA Code:
Sub Actuals_Formula()
'
' Actuals_Formula Macro
Dim Rnd As Range
Dim k As Variant
k = InputBox("Enter Week Number to find")
If k = "" Then Exit Sub
Range("A2").Activate
ActiveSheet.Range("2:2").AutoFilter Field:=2, Criteria1:="Actual"
With ActiveSheet
Set Rnd = .Rows(2).Find(What:=k, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
If Rnd Is Nothing Then
MsgBox "Week not found"
Exit Sub
End If
With Intersect(.UsedRange, Rnd.Columns, Rows(3))
With Selection.FormulaR1C1 = "=SUMIFS(Actuals!C9,Actuals!C4,R1C1,Actuals!C8,RC1)"
Selection.NumberFormat = "_-* #,##0.00_-;-* #,##0.00_-;_-* ""-""_-;_-@_-"
Selection.Copy
Range(Selection, Selection.End(xlDown).Offset(-1, 0)).Select
ActiveSheet.Paste
ActiveSheet.Range("2:2").AutoFilter Field:=2
End With
Application.CutCopyMode = False
End With
End With
End Sub
Thank you for your help, its driving me crazy !
ActiveSheet.Range("2:2").AutoFilter Field:=2, Criteria1:="Actual"
and insert the this formula and copy it down on all the filtered cells. This also needs to loop through each worksheet.
ActiveCell.FormulaR1C1 = "=SUMIFS(Actuals!C9,Actuals!C4,R1C1,Actuals!C8,RC1)"
Selection.NumberFormat = "_-* #,##0.00_-;-* #,##0.00_-;_-* ""-""_-;_-@_-"
Selection.Copy
Range(Selection, Selection.End(xlDown).Offset(-1, 0)).Select
ActiveSheet.Paste
I keep getting the object or with block error again.