How to match the number then replace with value in Column B then sum

[tuongtu3]

Hello All,
I'm trying to find the "number" in all ws then replace "number" by value in cell B then sum total at bottom of each row. Please see sample in attached.


It's similar to my previous thread:
http://www.excelforum.com/excel-prog...ml#post3869136


Regards,
tt3

[nilem]

maybe

Sub tt()
With Range("C2:H" & Cells(Rows.Count, 1).End(xlUp).Row)
    .Value = Evaluate("If(" & .Address & ">0," & .Address & "+ 4,"""")")
End With
End Sub

[tuongtu3]

Hi Nilem,
That's perfect and thank you very much for your help.

Regards,
tt3

[tuongtu3]

Sorry for re-open the thread becuase the code is not what I expected:

Hi Nilem:
The code is not working because the value on column B is not alway greader by 4. the value in column B could be any number. Please take a look and advise if possible:

maybe

Sub tt()
With Range("C2:H" & Cells(Rows.Count, 1).End(xlUp).Row)
    .Value = Evaluate("If(" & .Address & ">0," & .Address & "+ 4,"""")")


End With
End Sub

Regards,
tt3

[nilem]

Hi tt3,
try this

Sub ertert()
Dim x, i&, j&
x = Range("A1:H" & Cells(Rows.Count, 1).End(xlUp).Row).Value
With CreateObject("Scripting.Dictionary")
    .CompareMode = 1
    For i = 1 To UBound(x)
        .Item(x(i, 1)) = x(i, 2)
    Next i
    For i = 1 To UBound(x)
        For j = 3 To UBound(x, 2)
            If .Exists(x(i, j)) Then x(i, j) = .Item(x(i, j))
        Next j
    Next i
End With
Range("A1:H1").Resize(i - 1).Value = x
End Sub

[tuongtu3]

Hi Nilem,
The code work great but would you please modify a code to start searching "NUMBER" at column I and after and also the fn and Vale now is at Column B & C instead of A & B. Please take a look at sample in attached.

Regards.
tt3

[nilem]

try

Sub ertert()
Dim x, i&, j&, ubx&
x = Range("B1").CurrentRegion.Value
With CreateObject("Scripting.Dictionary")
    .CompareMode = 1
    For i = 1 To UBound(x)
        .Item(x(i, 1)) = x(i, 2)
    Next i

    ubx = UBound(x) + 1: x = Range("I1").CurrentRegion.Resize(ubx).Value
'    For i = 1 To UBound(x, 2): x(ubx, i) = 0: Next i
    For i = 1 To UBound(x)
        For j = 1 To UBound(x, 2)
            If .Exists(x(i, j)) Then x(i, j) = .Item(x(i, j)): x(ubx, j) = x(ubx, j) + x(i, j)
        Next j
    Next i
End With
Range("I1").Resize(UBound(x), UBound(x, 2)).Value = x
End Sub

[tuongtu3]

Hi Nilem,
Thank you very much for you time and help. The SUM from code is wrong b/c code does not sum any number in RED which they're not in column B. Please see sample in attached.

Regards,
tt3

[nilem]

Hi tt,
please check the attached file

[tuongtu3]

Hi Nilem,
Sorry I forgot to mention there's text in cell "C:I" so when I tried your code it works if those cells are empty but if there are text filled in then it does not work. Can you take a look at sample in attached and see:

Regards,
tt3

[nilem]

OK, try again

Sub ertert()
Dim x, i&, j&, ubx&
x = Range("A1:B" & Cells(Rows.Count, 1).End(xlUp).Row).Value
With CreateObject("Scripting.Dictionary")
    .CompareMode = 1
    For i = 2 To UBound(x): .Item(x(i, 1)) = x(i, 2): Next i
    ubx = UBound(x) + 1
    x = Range("J1", Cells(1, Columns.Count).End(xlToLeft)).Resize(ubx).Value
    For i = 1 To UBound(x, 2): x(ubx, i) = 0: Next i
    For i = 2 To UBound(x) - 1    '2
        For j = 1 To UBound(x, 2)
            If .Exists(x(i, j)) Then x(i, j) = .Item(x(i, j))
            x(ubx, j) = x(ubx, j) + x(i, j)
        Next j
    Next i
End With
Range("J1").Resize(UBound(x), UBound(x, 2)).Value = x
End Sub

[tuongtu3]

Hi Nilem,
Thank you very much for your time and help.

Regards,
tt3