CODEWITHSUNDEEP
excelvba
Troy Cosentino
Troy Cosentino

Reputation: 4778

excel adding cell values together and putting in a new one

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

Upvotes: 0

Views: 496

Answers (1)

Daniel
Daniel

Reputation: 13142

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)

Upvotes: 1

Related Questions