Insert Formula to Return Total

[MrThorne]

BACKGROUND:
I have been working on a complicated workbook for about a week now that charts multiple variables from daily feedback forms over time. I integrated vba forms into the spreadsheets to mimic the hard copy forms that would be used for input. Everything works fine except for when it comes to calculating totals from text boxes.

PROBLEM:
I currently have the form adding the values and returning a total. This means that if a value is miskeyed and the user corrects it in the spreadsheet, the total does not update as it is not dependent on the data from the cells, only on the data entered from the form.

The effect I am looking to achieve would be akin to pasting the formula for total into the proper cell when clicking the OK button on the form.

CONSTRAINTS:
-The user must be able to change a cell in the worksheet and have the total update
-You may not copy the formula sum=() into the cells (would cause conflict with form output)

OTHER:
The code that is currently checking for columns and totaling numbers follows the format:
(The very last line is where the total is calculated).

ColumnCount = Worksheets("DAILY DEFECT LOG").Range("A1").CurrentRegion.Columns.Count
  
    With Worksheets("DAILY DEFECT LOG").Range("A1")
        .Offset(0, ColumnCount).Value = DateValue(Me.txtDate.Value)
        .Offset(1, ColumnCount).Value = Me.cmbFloater.Value
        .Offset(2, ColumnCount).Value = Me.cmbLead.Value
      
        .Offset(3, ColumnCount).Value = Me.cmbM1.Value
        .Offset(4, ColumnCount).Value = Me.cmbM1Model.Value
        .Offset(5, ColumnCount).Value = Me.cmbM1Model2.Value
        .Offset(6, ColumnCount).Value = Me.cmbM1Printer.Value
        .Offset(7, ColumnCount).Value = Me.cmbM1Loader.Value
        .Offset(8, ColumnCount).Value = Me.txtM1Paint.Value
        .Offset(9, ColumnCount).Value = Me.txtM1Machine.Value
        .Offset(10, ColumnCount).Value = Me.txtM1OpPr.Value
        .Offset(11, ColumnCount).Value = Me.txtM1OpLd.Value
      
        Dim v1 As Integer
        Dim v2 As Integer
        Dim v3 As Integer
        Dim v4 As Integer
        v1 = Me.txtM1Paint.Value
        v2 = Me.txtM1Machine.Value
        v3 = Me.txtM1OpPr.Value
        v4 = Me.txtM1OpLd.Value

        .Offset(12, ColumnCount).Value = v1 + v2 + v3 + v4

 

[Luke M]

With just that snippet, I'm not completely sure what's going on. But, it sounds like we need to be able to make a change to the worksheet and have a cell, which has not formula, get updated. If that's true, I'd recommend using the Worksheet_Change event to trap the event, and then you could check which cell(s) got changed and update from there.
Psuedo code

Private Sub Worksheet_Change(Target as Variant)
If Intersect(Target,Range("A:D")) Is Nothing Then Exit Sub

Dim myRow as Long
myRow = Target.Row
Cells(myRow,10).Value = Cells(myRow,1) + Cells(myRow,2) + Cells(myRow,3)
End Sub

 

[MrThorne]

I will be honest with you, I have been using vba and macros for all of 3 days now and am not entirely sure how to use that. I deleted and changed much of my spreadsheet to post, and have attached it below. When you click the "Reject Tally Sheet" button, you are presented with a form. I would like for the form to insert a formula into G10 instead of adding the cells above it as it is doing now. This would make it so that the end user can type in number via the form, or directly into the cells.

Everything I know about vba, I was able to figure out through
http://www.fontstuff.com/ebooks/free/fsuserforms.pdf
and a couple of Google searches. I don't know anything beyond that other than what I want the form to do...

 

[Ananda Kumar C]

Hi,

Where you update the sum (in column G I believe), please use this:

.Offset(12, ColumnCount).Value = "=SUM(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),,-4,,4))"

instead of

.Offset(12, ColumnCount).Value = v1 + v2 + v3 + v4

Thanks,
Anand

 

[Luke M]

Or, if you don't want to end up with a volatile function (due to the INDIRECT), you can write:

.OFfset(12, ColumnCount).FormulaR1C1 = "=SUM(RC[-4]:RC[-1])"

 

[MrThorne]

This almost worked, it should have been:

.Offset(12, ColumnCount).FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"

for what I was trying to accomplish. Thank you very much for sharing this feature, I was able to apply it in other locations throughout the document as well. Very smooth.