code is replacing value, but should adding instead


This vba takes a code from sheet4 and searches the match through sheet2. If finds it, then copies the corresponding value from sheet4 to sheet2.

(the code, col i) (the value, col K)
123456789 [color=Red]200[/color]


(the code, in one of the columns c, e:m) (the value, col AD)
123456789 [color=Red]value to here[/color]

And the code itself:

[code]Private Sub searchMATCH()

Dim a, b(), i As Long
With Sheets("sheet4")
a = .Range("i1", .Range("i" & Rows.Count).End(xlUp)).Resize(, 3).Value
End With
With CreateObject("Scripting.Dictionary")
.CompareMode = vbTextCompare
For i = 1 To UBound(a, 1)
For ii = 1 To 11
.Item(a(i, 1)) = a(i, 3)
With Sheets("sheet2")
a = .Range("c1", .Range("c" & Rows.Count).End(xlUp)).Resize(, 11).Value
End With
ReDim b(1 To UBound(a, 1), 1 To 1)
For i = 1 To UBound(a, 1)
For ii = 1 To UBound(a, 2)
If .exists(a(i, ii)) Then
b(i, 1) = .Item(a(i, ii))
Exit For
End If
End With
Sheets("sheet2").Range("ad1").Resize(UBound(b, 1)).Value = b
End Sub

But the problem is, if there are more than 1 matching codes on sheet4(only) then the vba replaces the value with the last corresponding value.
But i need it to be added on to last value (+).

On sheet4: VALUE
123456789 200
123456789 300

Then it should be on sheet2 not 300 but 200[b]+300[/b] (eg 500)

Can anyone fix this?

Than you!


Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!