# Sum of UserForm TextBoxes doesn't work over 999.99



## Darranimo (Dec 20, 2022)

I have a userform that has several text boxes that I want to sum and then place in a cell on the worksheet. It works great until any of the four text boxes equals 1,000 or more. Here is the beginning of my code. I have bolded the line that isn't working properly. I'm thinking it has to do with a limitation of the Val() function that I am unaware of. Any help would be greatly appreciated! Thanks!


```
Private Sub cmdAdd_click()
    Dim lrw As Long
    Dim acell As Range
    Dim rng As Range
    Dim ws As Worksheet
    Dim comp As String
    On Error Resume Next
   
    lrw = Cells.Find(What:="*", After:=Range("A1"), LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
    comp = Me.cboCompany.Value
   
    Range("dDComp").Value = "'" & Left(comp, 8)
    Range("dDCompName").Value = comp
    Set ws = Sheets("Line Types")
    Set acell = Worksheets("GL Import Data Batches").Range("A" & lrw + 1)
    Set rng = ws.Range("Rebate")
   
    If Left(comp, 4) = "2311" Then Range("dLH").Value = "H"
    If Left(comp, 4) = "2310" Then Range("dLH").Value = "L"
          
    rng.Copy acell
   
    acell.Offset(0, 17).Value = txtAmount1.Value
    acell.Offset(4, 17).Value = txtAmount2.Value
    acell.Offset(8, 17).Value = txtAmount3.Value
    acell.Offset(12, 17).Value = txtAmount4.Value
    acell.Offset(16, 17).Value = txtAmount5.Value
    acell.Offset(20, 17).Value = txtAmount6.Value
*    acell.Offset(24, 17).Value = Val(txtAmount7.Value) + Val(txtAmount8.Value) + Val(txtAmount9.Value) + Val(txtAmount10.Value)*
    acell.Offset(26, 17).Value = txtAmount7.Value
    acell.Offset(30, 17).Value = txtAmount8.Value
    acell.Offset(34, 17).Value = txtAmount9.Value
    acell.Offset(38, 17).Value = txtAmount10.Value
```


----------



## Z51 (Dec 20, 2022)

You may want to remove the 'On Error Resume Next' at the beginning, or add 'On Error GoTo 0' before any of the cells are assigned a value (e.g. acell.Offset(0, 17).....) to see if an error comes up at that point.  I created a simple form with four textboxes and had no issue entering or summing large numbers and assigning it to a cell value.


----------



## Darranimo (Dec 20, 2022)

Z51 said:


> You may want to remove the 'On Error Resume Next' at the beginning, or add 'On Error GoTo 0' before any of the cells are assigned a value (e.g. acell.Offset(0, 17).....) to see if an error comes up at that point.  I created a simple form with four textboxes and had no issue entering or summing large numbers and assigning it to a cell value.


I tried that and still no luck. I do have the following coding for the textboxes after updating. Would this affect it at all? I can't see how...


```
Private Sub txtAmount1_AfterUpdate()
    Dim txt As Object
    Set txt = Me.txtAmount1
        
    With txt
        If IsNumeric(.Value) Then .Value = Format(.Value, "#,##0.00")
        If txt.Value = "" Then txt.Value = "0.00"
    End With
End Sub
```

Here is the full code for hopefully more context:


```
Private Sub cmdAdd_click()
    Dim lrw As Long
    Dim acell As Range
    Dim rng As Range
    Dim ws As Worksheet
    Dim comp As String
    'On Error Resume Next
    
    lrw = Cells.Find(What:="*", After:=Range("A1"), LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
    comp = Me.cboCompany.Value
    
    Range("dDComp").Value = "'" & Left(comp, 8)
    Range("dDCompName").Value = comp
    Set ws = Sheets("Line Types")
    Set acell = Worksheets("GL Import Data Batches").Range("A" & lrw + 1)
    Set rng = ws.Range("Rebate")
    
    If Left(comp, 4) = "2311" Then Range("dLH").Value = "H"
    If Left(comp, 4) = "2310" Then Range("dLH").Value = "L"
           
    rng.Copy acell
    
    acell.Offset(0, 17).Value = txtAmount1.Value
    acell.Offset(4, 17).Value = txtAmount2.Value
    acell.Offset(8, 17).Value = txtAmount3.Value
    acell.Offset(12, 17).Value = txtAmount4.Value
    acell.Offset(16, 17).Value = txtAmount5.Value
    acell.Offset(20, 17).Value = Val(txtAmount6.Value)
    acell.Offset(24, 17).Value = Val(txtAmount7.Value) + Val(txtAmount8.Value) + Val(txtAmount9.Value) + Val(txtAmount10.Value)
    acell.Offset(26, 17).Value = txtAmount7.Value
    acell.Offset(30, 17).Value = txtAmount8.Value
    acell.Offset(34, 17).Value = txtAmount9.Value
    acell.Offset(38, 17).Value = txtAmount10.Value
    
    acell.Offset(0, 18).Value = txtSupplier.Value & " " & txtReference.Value
    acell.Offset(4, 18).Value = txtSupplier.Value & " " & txtReference.Value
    acell.Offset(8, 18).Value = txtSupplier.Value & " " & txtReference.Value
    acell.Offset(12, 18).Value = txtSupplier.Value & " " & txtReference.Value
    acell.Offset(16, 18).Value = txtSupplier.Value & " " & txtReference.Value
    acell.Offset(20, 18).Value = txtSupplier.Value & " " & txtReference.Value
    acell.Offset(24, 18).Value = txtSupplier.Value & " " & txtReference.Value
    acell.Offset(26, 18).Value = txtSupplier.Value & " " & txtReference.Value
    acell.Offset(30, 18).Value = txtSupplier.Value & " " & txtReference.Value
    acell.Offset(34, 18).Value = txtSupplier.Value & " " & txtReference.Value
    acell.Offset(38, 18).Value = txtSupplier.Value & " " & txtReference.Value

    lrw = Cells.Find(What:="*", After:=Range("A1"), LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
    Range("A8", "T" & lrw).Select
    Selection.Copy
    Range("A8").PasteSpecial xlPasteValues

    lrw = Cells.Find(What:="*", After:=Range("A1"), LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
    Set acell = Worksheets("GL Import Data Batches").Range("A" & lrw + 1)
    For rng2 = lrw To 8 Step -1
        If Cells(rng2, 18) = 0 Then
            Rows(rng2).Delete
        End If
    Next
    
    acell.Select
    
End Sub
```


----------



## Darranimo (Dec 20, 2022)

Darranimo said:


> I tried that and still no luck. I do have the following coding for the textboxes after updating. Would this affect it at all? I can't see how...
> 
> 
> ```
> ...


Also, if you look at the red line above. I added the Val() function to it. The result is the same... up to 999.99 it returns correctly. 1,000 and above the result is divided by 1,000.


----------



## Darranimo (Dec 20, 2022)

I resolved it!!! I need to use CDbl instead of Val since my textbox formatting had a comma.


----------

