excel adding cell values together and putting in a new one

Question

here is the code im trying:

Set found = Worksheets("Result").Range("A:A").Find(Prefix, , xlValues, xlWhole)
found.Offset(0, 1).Value = CInt(found.Offset(0, 1).Value) + CInt(C.Offset(0, 1).Value)

Prefix is a 3 digit number that is found in a column, what i then want to do is increase the cell to the right of where it is found by a cell one to the right of "C". I am getting a type mismatch error.

here is where c comes from:

For Each C In Worksheets("AMZ").Range("C2:C" & endRow).Cells

any ideas?

EDIT: Full code

Sub processData()

Dim endRow As Variant
endRow = Worksheets("AMZ").Range("A65536").End(xlUp).Row

For Each C In Worksheets("AMZ").Range("C2:C" & endRow).Cells
    Dim found As Range
    Prefix = C.Value
    C.Select

    'remove prefix
    If Not Left(Prefix, 3) = "FBA" Then
        'nothing
        If Mid(Prefix, 3, 1) = "-" Then
            Prefix = Left(Prefix, 2)
        ElseIf Mid(Prefix, 4, 1) = "-" Then
            Prefix = Left(Prefix, 3)
        Else
            Prefix = "-1"
        End If

        If Not Prefix = "-1" Then
            Set found = Worksheets("Result").Range("A:A").Find(Prefix, , xlValues, xlWhole)
            found.Offset(0, 1).Value = CInt(Val(found.Offset(0, 1).Value)) + CInt(Val(C.Offset(0, 1).Value))
        End If
    End If
Next

End Sub

What values do you have in the in the found.offset(0,1) and the c.offset(0,1) ranges? — nutsch
– nutsch, Commented Sep 25, 2012 at 0:48

Daniel · Accepted Answer · 2012-09-25 02:48:52Z

1

If found.Offset(0, 1).Value) or CInt(C.Offset(0, 1).Value) are string values, like "" or "1234hello" or anything that does not directly translate to an integer, you get the error you listed.

The easiest way to correct this, is to surround the value with the Val(string) function. In your circumstance, you would use the following

found.Offset(0, 1).Value = CInt(Val(found.Offset(0, 1).Value)) + CInt(Val(C.Offset(0, 1).Value))

A note about Val, if the values you provide could not possibly be a number, it will default to 0. Otherwise it will take any numbers at the beginning of the string offered. For more about Val read the link. (same as above)

answered Sep 25, 2012 at 2:48

Daniel

13.1k3 gold badges39 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Troy Cosentino Over a year ago

this is giving me a overflow error now.. I will post all my code in my question

Daniel Over a year ago

Try using CLng instead of CInt. CInt fails if the value passed is >=32768.

Collectives™ on Stack Overflow

excel adding cell values together and putting in a new one

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related