VBA to add SUM formula to cell using 2 variables, and also name the cell

[Pianostool]

Hi folks.

I'm trying to put a SUM formula into cell B2 via VBA.

The range of the SUM can vary depending on inputs, so I can't hard cade any cell references into it.

So, the formula I need in B2 is something like:

=SUM(Variable1:Variable2)

Variable1 will be something like "Range("A" & Position1.Row).Offset(2)"

Variable2 will be something like "Range("A" & Position2.Row).Offset(2)"

Position1 and Position2 are cells defined via a search for specific texts. So I have them already defined and working.

Also, once that cell has the formula in it from the code, I would like that cell named as SENDTOTAL.

Any help would be appreciated.

Thanks in advance.

 

[bebo021999]

I run a demo, with the 2 pos are A4 and A20:

Option Explicit
Sub Sumrng()
Dim position1 As Range, position2 As Range
Set position1 = Range("A4")
Set position2 = Range("A20")
Set position1 = position1.Offset(2)
Set position2 = position2.Offset(2)
With Range("B2")
    .Formula = "=SUM(" & position1.Address & ":" & position2.Address & ")"
    .Name = "SENDTOTAL"
End With
End Sub

with your position1 and position are defined:

With Range("B2")
    .Formula = "=SUM(" & position1.Address & ":" & position2.Address & ")"
    .Name = "SENDTOTAL"
End With

 

[kvsrinivasamurthy]

Try.

Range("B2").Formula = "=SUM(A" & Position1.Row + 2 & ":A" & Position2.Row + 2 & ")"
Range("B2").name= "SENDTOTAL"

 

[Pianostool]

Awesome @bebo021999 - I was able to use your info to get it working.

Had some other adjustments to do because of my data, but your whole principle worked really well.

Thank you.