exitSandMan
New Member
- Joined
- Sep 14, 2022
- Messages
- 2
- Office Version
- 365
- Platform
- Windows
Hello all! I'm running into an error at the 'Set aa' line: "Run-time error '5': Invalid procedure call or argument".
What I'm trying to do is have the Month variable be used to find the corresponding cell in two separate sections of the worksheet (prior month "BU2:CG411" vs current month "CI2:CU411") to then subtract them and paste the formula down a variance column('DY').
My apologies if this is a silly question/error I'm running into... I just recently started playing around with VBA after realizing the huge potential for automation! Thank you in advance!!!
What I'm trying to do is have the Month variable be used to find the corresponding cell in two separate sections of the worksheet (prior month "BU2:CG411" vs current month "CI2:CU411") to then subtract them and paste the formula down a variance column('DY').
My apologies if this is a silly question/error I'm running into... I just recently started playing around with VBA after realizing the huge potential for automation! Thank you in advance!!!
VBA Code:
Sub test()
Dim Month As Variant
Month = InputBox("Enter current month abbreviation (e.g. Jan)", "Historical/Actual/CE Column Formatting")
With ActiveSheet.Range("CI2:CU411")
Set a = .Find(Month, LookIn:=xlValues)
Set aa = ActiveSheet.Cells(a.Address).Offset(1, 0)
End With
With ActiveSheet.Range("BU2:CG411")
Set b = .Find(Month, LookIn:=xlValues)
aa = ActiveSheet.Cells(a.Address).Offset(1, 0)
End With
Range("DY3").Select
ActiveCell.FormulaR1C1 = aa - bb
Selection.Copy
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.SpecialCells(xlCellTypeFormulas, 23).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Sub