.Formula subtracting cell values instead of relative cells

exitSandMan 0
Viewed 21

I am trying to set cell DY3 equal to a.Address - b.Address (as in, CP3 - CB3), but instead I am getting the actual value of these cells (as in, 53-50).

While the final value would technically be the same, I do not want the result hardcoded.

I thought setting the .Formula property of the desired range equal to the .Address properties would return the relative cells and not the actual values within the cells.

Any help would be greatly appreciated!

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).Offset(1, 0)
    End With

    With ActiveSheet.Range("BU2:CG411")
        Set b = .Find(Month, LookIn:=xlValues).Offset(1, 0)
    End With
    
        Range("DY3").Formula = Range(a.Address) - Range(b.Address)
        
        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
vba excel
1 Answers

IIUC, the following:

Range("DY3").Formula = "=" & a.Address & "-" & b.Address

If you want the addresses to be relative, then:

Range("DY3").Formula = "=" & a.Address(False, False) & "-" & b.Address(False, False)
BigBen0
Related