# Thread: Lookup and summ... (2000)

1. ## Lookup and summ... (2000)

In this sheet are present value in column E and F.
I want to loop only the value in column E, tath have the in right the number "2" (for example CB0500 2 1,CB050022,CB050023,CB050025...CB681121).
Condition:
for value = CB050121
summ 30 new value = CB050151
loop thsi new value in all column E if existis summ the value in column F refered line
in this case the my_var_TOTAL = 2.289.280+827.130
eccc...

2. ## Re: Lookup and summ... (2000)

You write have the in right the number "2" but your examples have 2 in the next to last position. <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>
What do you mean by summ 30 new value = CB050151

Please try to explain much more clearly what you want.

3. ## Re: Lookup and summ... (2000)

i am sorry..

have in the position right 2 the number "2" (CB.....2.)

for summ...
example CB0500 21 sum the last 2 number + 30 ( 21 +30=51)

4. ## Re: Lookup and summ... (2000)

Can you explain how you want to use the calculation?

5. ## Re: Lookup and summ... (2000)

Does this code do what you want?

Steve

<pre>Option Explicit
Sub SumSAL()
Dim my_var_TOTAL As Double
Dim rngE As Range
Dim rCell As Range
Dim AWF As WorksheetFunction

Set rngE = Range(Range("e2"), _
Cells(Cells.Rows.Count, "E").End(xlUp))

Set AWF = Application.WorksheetFunction
my_var_TOTAL = 0
For Each rCell In rngE
If Mid(rCell, Len(rCell) - 1, 1) = "2" Then
my_var_TOTAL = my_var_TOTAL + rCell.Offset(0, 1) + _
AWF.SumIf(rngE, AWF.Replace(rCell, Len(rCell) - 1, 1, "5"), rngE.Offset(0, 1))
End If
Next
MsgBox "Total is: " & my_var_TOTAL
Set AWF = Nothing
Set rCell = Nothing
Set rngE = Nothing
End Sub</pre>

6. ## Re: Lookup and summ... (2000)

GOOD!
Tks.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•