CODEWITHSUNDEEP
vbaexcel
Graham Chandler
Graham Chandler

Reputation: 193

Excel Vba: formula values in cell

This should be pretty simple. Below is the code im working with:

Private Sub CommandButton23_Click()

Dim linerngs As Range
Dim lineitem As Range
Dim lastlinerow As Long
Dim TabLastRow
Dim claimstab As String
Dim officesrange As Range
Dim office As Range

officeslastrow = Sheet2.Range("A" & Rows.Count).End(xlUp).Row
lastlinerow = Sheet2.Range("D" & Rows.Count).End(xlUp).Row

Set officerng = Sheet2.Range("A6:A" & officeslastrow).SpecialCells(xlCellTypeConstants, 23)
Set linerngs = Sheet2.Range("D7:D" & lastlinerow).SpecialCells(xlCellTypeConstants, 23)

'Starts evaluating the first line item at the first home office
For Each lineitem In linerngs

        'evaluates line item to identify which formula is needed to count it
        If InStr(1, lineitem.Value, "IN") > 0 And InStr(1, lineitem.Value, "AOS") = 0 Then
        lineitem.Offset(0, 16).Formula = "=SUMPRODUCT(COUNTIFS(INDIRECT(""'""&R2C5&"" Claims'!B:B""),R[0]C[-19],INDIRECT(""'""&R2C5&"" Claims'!C:C""),R[0]C[-17],INDIRECT(""'""&R2C5&"" Claims'!E:E""),CHOOSE({1;2},R[0]C[-15],R[0]C[-14]),INDIRECT(""'""&R2C5&"" Claims'!G:G""),R[0]C[-9]:R[0]C[-1]))"
        lineitem.Offset(0, 17).Formula = "=SUMPRODUCT(COUNTIFS(INDIRECT(""'""&R3C5&"" Claims'!B:B""),R[0]C[-20],INDIRECT(""'""&R3C5&"" Claims'!C:C""),R[0]C[-18],INDIRECT(""'""&R3C5&"" Claims'!E:E""),CHOOSE({1;2},R[0]C[-16],R[0]C[-15]),INDIRECT(""'""&R3C5&"" Claims'!G:G""),R[0]C[-10]:R[0]C[-2]))"
        End If
        If InStr(1, lineitem.Value, "IN") > 0 And InStr(1, lineitem.Value, "AOS") > 0 Then
        lineitem.Offset(0, 16).Formula = "=SUMPRODUCT(COUNTIFS(INDIRECT(""'""&R2C5&"" Claims'!B:B""),R[0]C[-19],INDIRECT(""'""&R2C5&"" Claims'!C:C""),R[0]C[-17],INDIRECT(""'""&R2C5&"" Claims'!E:E""),CHOOSE({1;2},R[0]C[-15],R[0]C[-14])))-SUMPRODUCT(COUNTIFS(INDIRECT(""'""&R2C5&"" Claims'!B:B""),R[0]C[-19],INDIRECT(""'""&R2C5&"" Claims'!C:C""),R[0]C[-17],INDIRECT(""'""&R2C5&"" Claims'!E:E""),CHOOSE({1;2},R[0]C[-15],R[0]C[-14]),INDIRECT(""'""&R2C5&"" Claims'!G:G""),R2C11:R2C20))"
        lineitem.Offset(0, 17).Formula = "=SUMPRODUCT(COUNTIFS(INDIRECT(""'""&R3C5&"" Claims'!B:B""),R[0]C[-20],INDIRECT(""'""&R3C5&"" Claims'!C:C""),R[0]C[-18],INDIRECT(""'""&R3C5&"" Claims'!E:E""),CHOOSE({1;2},R[0]C[-16],R[0]C[-15])))-SUMPRODUCT(COUNTIFS(INDIRECT(""'""&R3C5&"" Claims'!B:B""),R[0]C[-20],INDIRECT(""'""&R3C5&"" Claims'!C:C""),R[0]C[-18],INDIRECT(""'""&R3C5&"" Claims'!E:E""),CHOOSE({1;2},R[0]C[-16],R[0]C[-15]),INDIRECT(""'""&R3C5&"" Claims'!G:G""),R2C11:R2C20))"
        End If
        If InStr(1, lineitem.Value, "Excess MO") > 0 And InStr(1, lineitem.Value, "AOS") = 0 Then
        lineitem.Offset(0, 16).Formula = "=SUMPRODUCT(COUNTIFS(INDIRECT(""'""&R2C5&"" Claims'!B:B""),R[0]C[-19],INDIRECT(""'""&R2C5&"" Claims'!C:C""),R[0]C[-17],INDIRECT(""'""&R2C5&"" Claims'!E:E""),CHOOSE({1;2},R[0]C[-15],R[0]C[-14]),INDIRECT(""'""&R2C5&"" Claims'!P:P""),"">""&R2C22,INDIRECT(""'""&R2C5&"" Claims'!G:G""),R[0]C[-9]:R[0]C[-1]))"
        lineitem.Offset(0, 17).Formula = "=SUMPRODUCT(COUNTIFS(INDIRECT(""'""&R3C5&"" Claims'!B:B""),R[0]C[-20],INDIRECT(""'""&R3C5&"" Claims'!C:C""),R[0]C[-18],INDIRECT(""'""&R3C5&"" Claims'!E:E""),CHOOSE({1;2},R[0]C[-16],R[0]C[-15]),INDIRECT(""'""&R3C5&"" Claims'!P:P""),"">""&R2C22,INDIRECT(""'""&R3C5&"" Claims'!G:G""),R[0]C[-10]:R[0]C[-2]))"
        End If
        If InStr(1, lineitem.Value, "Excess MO") > 0 And InStr(1, lineitem.Value, "AOS") > 0 Then
        lineitem.Offset(0, 16).Formula = "=SUMPRODUCT(COUNTIFS(INDIRECT(""'""&R2C5&"" Claims'!B:B""),R[0]C[-19],INDIRECT(""'""&R2C5&"" Claims'!C:C""),R[0]C[-17],INDIRECT(""'""&R2C5&"" Claims'!E:E""),CHOOSE({1;2},R[0]C[-15],R[0]C[-14]),INDIRECT(""'""&R2C5&"" Claims'!P:P""),"">""&R2C22))-SUMPRODUCT(COUNTIFS(INDIRECT(""'""&R2C5&"" Claims'!B:B""),R[0]C[-19],INDIRECT(""'""&R2C5&"" Claims'!C:C""),R[0]C[-17],INDIRECT(""'""&R2C5&"" Claims'!E:E""),CHOOSE({1;2},R[0]C[-15],R[0]C[-14]),INDIRECT(""'""&R2C5&"" Claims'!P:P""),"">""&R2C22,INDIRECT(""'""&R2C5&"" Claims'!G:G""),R2C11:R2C20))"
        lineitem.Offset(0, 17).Formula = "=SUMPRODUCT(COUNTIFS(INDIRECT(""'""&R3C5&"" Claims'!B:B""),R[0]C[-20],INDIRECT(""'""&R3C5&"" Claims'!C:C""),R[0]C[-18],INDIRECT(""'""&R3C5&"" Claims'!E:E""),CHOOSE({1;2},R[0]C[-16],R[0]C[-15]),INDIRECT(""'""&R3C5&"" Claims'!P:P""),"">""&R2C22))-SUMPRODUCT(COUNTIFS(INDIRECT(""'""&R3C5&"" Claims'!B:B""),R[0]C[-20],INDIRECT(""'""&R3C5&"" Claims'!C:C""),R[0]C[-18],INDIRECT(""'""&R3C5&"" Claims'!E:E""),CHOOSE({1;2},R[0]C[-16],R[0]C[-15]),INDIRECT(""'""&R3C5&"" Claims'!P:P""),"">""&R2C22,INDIRECT(""'""&R3C5&"" Claims'!G:G""),R2C11:R2C20))"
        End If
        If InStr(1, lineitem.Value, "Medical Only") > 0 Then
        lineitem.Offset(0, 16).Formula = "=SUMPRODUCT(COUNTIFS(INDIRECT(""'""&R2C5&"" Claims'!B:B""),R[0]C[-19],INDIRECT(""'""&R2C5&"" Claims'!C:C""),R[0]C[-17],INDIRECT(""'""&R2C5&"" Claims'!E:E""),CHOOSE({1;2},R[0]C[-15],R[0]C[-14]),INDIRECT(""'""&R2C5&"" Claims'!P:P""),""<""&R2C22))"
        lineitem.Offset(0, 17).Formula = "=SUMPRODUCT(COUNTIFS(INDIRECT(""'""&R3C5&"" Claims'!B:B""),R[0]C[-20],INDIRECT(""'""&R3C5&"" Claims'!C:C""),R[0]C[-18],INDIRECT(""'""&R3C5&"" Claims'!E:E""),CHOOSE({1;2},R[0]C[-16],R[0]C[-15]),INDIRECT(""'""&R3C5&"" Claims'!P:P""),""<""&R2C22))"
        End If
        If InStr(1, lineitem.Value, "Incident Only") > 0 Then
        lineitem.Offset(0, 16).Formula = "=SUMPRODUCT(COUNTIFS(INDIRECT(""'""&R2C5&"" Claims'!B:B""),R[0]C[-19],INDIRECT(""'""&R2C5&"" Claims'!C:C""),R[0]C[-17],INDIRECT(""'""&R2C5&"" Claims'!E:E""),CHOOSE({1;2},R[0]C[-15],R[0]C[-14])))"
        lineitem.Offset(0, 17).Formula = "=SUMPRODUCT(COUNTIFS(INDIRECT(""'""&R3C5&"" Claims'!B:B""),R[0]C[-20],INDIRECT(""'""&R3C5&"" Claims'!C:C""),R[0]C[-18],INDIRECT(""'""&R3C5&"" Claims'!E:E""),CHOOSE({1;2},R[0]C[-16],R[0]C[-15])))"
        End If

Next lineitem
'repeats the process for the next line item until it reaches the end

'Calculates Net Claims
For Each lineitem In linerngs
lineitem.Offset(0, 19).Formula = "=R[0]C[-3] - SUM(R[0]C[-2]:R[0]C[-1])"
Next lineitem

End Sub

Everything works fine I would just prefer it if only the formula result was displayed in the cell, not the entire formula. I know this is possible I just cant remember how I did it in the past. any help would be greatly appreciated.

Upvotes: 0

Views: 4029

Answers (2)

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

Rather than something like:

Range("C1").Formula = "=SUM(A1,B1)"

use:

Range("C1").Value = Evaluate("=SUM(A1,B1)")

Upvotes: 0

E. Monk
E. Monk

Reputation: 378

There are a variety of ways to do this.

Personally I would add setting the cell value equal to its own value after you set the formula. See below for example snippet:

If InStr(1, lineitem.Value, "Incident Only") > 0 Then
    lineitem.Offset(0, 16).Formula = "=SUMPRODUCT(COUNTIFS(INDIRECT(""'""&R2C5&"" Claims'!B:B""),R[0]C[-19],INDIRECT(""'""&R2C5&"" Claims'!C:C""),R[0]C[-17],INDIRECT(""'""&R2C5&"" Claims'!E:E""),CHOOSE({1;2},R[0]C[-15],R[0]C[-14])))"
    lineitem.Offset(0, 16).Value = lineitem.Offset(0, 16).Value
    lineitem.Offset(0, 17).Formula = "=SUMPRODUCT(COUNTIFS(INDIRECT(""'""&R3C5&"" Claims'!B:B""),R[0]C[-20],INDIRECT(""'""&R3C5&"" Claims'!C:C""),R[0]C[-18],INDIRECT(""'""&R3C5&"" Claims'!E:E""),CHOOSE({1;2},R[0]C[-16],R[0]C[-15])))"
    lineitem.Offset(0, 17).Value = lineitem.Offset(0, 17).Value
End If

Upvotes: 1

Related Questions