(VBA) Having trouble assigning relative cells (from an InputBox) to variables in order to subtract them.

[exitSandMan]

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!!!

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

 

[exitSandMan]

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!!!

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)
        bb = ActiveSheet.Cells(b.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

Apologies, was in the middle of tinkering with the code and didn't fix variable names.