Results 1 to 6 of 6
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup and summ... (2000)

    Can you explain how you want to use the calculation?

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #6
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •