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

    SUMM PARTICOLAR (2000 sr 1)

    This file is a result of importing data from a CICS TERMINAL application. (The range C9:G3500 it must remain in text format)
    My probs are:

    1) clororize (in Red color) and format bold line range A:G if in the column A is present TOT. (if the subtotal are blank colorize only in the simple Red color. for example, TOT. SETT. 05, TOT. SETT. 08...)

    2) summ all line, if is present column A is present TOT. and insert the result in the line 6

    Tks as usual.

  2. #2
    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: SUMM PARTICOLAR (2000 sr 1)

    <P ID="edit" class=small>(Edited by sdckapr on 27-Apr-04 08:59. Added sum formula Attached new file)</P>See the attached. I used 2 conditions for cond formatting:
    BOLD/RED when
    =AND(TRIM($C9)<>"",ISNUMBER(SEARCH("TOT.",$A9)))

    Just red when
    and
    =ISNUMBER(SEARCH("TOT.",$A9))

    Steve
    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> FOrgot the summing question. I used an ARRAY formula (ctrl-shift-enter to confirm)
    In C6 and copy to D6:G6
    =SUM(IF(NOT(ISERROR(VALUE(TRIM(C9:C284)))),VALUE(T RIM(C9:C284))))

  3. #3
    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: SUMM PARTICOLAR (2000 sr 1)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> another "OOPS". I forgot to exclude the subtotals in the full sum:Use this in C6 and copy it (array formula, confirm with ctrl-shift-enter)

    =SUM(IF(NOT(ISERROR(VALUE(TRIM(C9:C284))))*(LEFT($ A9:$A284,4)<>"TOT."),VALUE(TRIM(C9:C284))))

    Steve

  4. #4
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUMM PARTICOLAR (2000 sr 1)

    argggggggggggg.
    Difficult to make new formulas, re-attache my file, please.

    =SOMMA(SE(NON(VAL.ERRORE(VALORE(ANNULLA.SPAZI(C9:C 284))))*(SINISTRA($A9:$A284;4)<>"TOT.");VALORE(ANN ULLA.SPAZI(C9:C284))))

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUMM PARTICOLAR (2000 sr 1)

    I take this value and put this in the cell, my prob is to format this cell to left when the value is grabbed

    The piece of macro is in the attachement... line 124

    Range("A" + RIGA2) = "TOT. SETT. " & SETTORE

    My dear, tks for all. You are very, very BEST.

  6. #6
    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: SUMM PARTICOLAR (2000 sr 1)

    Here it is.

    Steve

  7. #7
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUMM PARTICOLAR (2000 sr 1)

    WAnderfull, ! Only You!

    have you see the second post to refer the left format?

  8. #8
    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: SUMM PARTICOLAR (2000 sr 1)

    I saw it, but I am not sure what you are asking: I see no question.

    Steve

  9. #9
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUMM PARTICOLAR (2000 sr 1)

    I take this value and put this in the cell, my prob is to format this cell to left when the value is grabbed

    The piece of macro is in the attachement... line 124

    Range("A" + RIGA2) = "TOT. SETT. " & SETTORE

    My dear, tks for all. You are very, very BEST.

  10. #10
    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: SUMM PARTICOLAR (2000 sr 1)

    You just copied your previous post. I didn't understand what you were asking when you first posted it <img src=/S/grin.gif border=0 alt=grin width=15 height=15>.

    I don't see a question and am not sure what the problem you are asking about is.

    Is it related to the coloring of the Tot cells and the cond formatting?
    Is it related to sum formula I gave you?
    Or is this a different problem all together?

    If you are trying to left align, use something like this:
    Range("A" + RIGA2).HorizontalAlignment = xlLeft

    Note your "numbers" can not be left aligned, unless you remove the spaces first.

    Steve

Posting Permissions

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