Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Insert value into cell ... (2000 sr 1)

    i have this sheet PERDITE with many line...
    This line into sheet are updatiing with other macro, in effect the number of line are variable...
    My problem is:

    To insert after the last cell filled into column F the string "TOTALE" (in Font.Bold ) and into last cell of column G the summ of value range G3:G...

  2. #2
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert value into cell ... (2000 sr 1)

    Sal,

    Have you thought about putting the "Total" line and cell that sums up the column at the top of the sheet? It is a different way of looking at the totals and there is no need to code the total or sum formula.

    Regards,
    John

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert value into cell ... (2000 sr 1)

    <code>
    Public Sub InsertTotale()
    Dim lLast As Long
    With Worksheets("PERDITE")
    lLast = .Range("F65536").End(xlUp).Row
    .Range("F1").Offset(lLast, 0).Value = "TOTALE"
    .Range("G1").Offset(lLast, 0).Formula = "=SUM(G1:G" & lLast & ")"
    End With
    End Sub
    </code>
    Legare Coleman

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

    Re: Insert value into cell ... (2000 sr 1)

    Hi, Legare Coleman
    have error 1004 on line:

    With Worksheets("PERDITE")

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

    Re: Insert value into cell ... (2000 sr 1)

    ok solved prob for error 1004...
    But the column G is in text formt, and i would want to maitain this format....
    Into last cel of column G the macro put this:=Sum(G1:G10)
    and not the real summ...
    How to...

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert value into cell ... (2000 sr 1)

    If column G is text, how is the code supposed to get a sum?
    Legare Coleman

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

    Re: Insert value into cell ... (2000 sr 1)

    {=SOMMA(VALORE(G3:G))}

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert value into cell ... (2000 sr 1)

    OK, so they are numbers stored as text? Try:

    <code>
    Option Explicit

    Public Sub InsertTotale()
    Dim lLast As Long, I As Long, dSum As Double
    With Worksheets("PERDITE")
    lLast = .Range("F65536").End(xlUp).Row
    dSum = 0
    For I = 2 To lLast - 1
    dSum = dSum + CDbl(.Range("G1").Offset(I, 0).Value)
    Next I
    .Range("F1").Offset(lLast, 0).Value = "TOTALE"
    .Range("G1").Offset(lLast, 0).Value = dSum
    End With
    End Sub
    </code>
    Legare Coleman

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

    Re: Insert value into cell ... (2000 sr 1)

    Hi, Legare Coleman
    the code work perfect!
    ... Is possible to summ all value into column G, and exlcude from summ the value "NN" if this is present?
    Sal.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Insert value into cell ... (2000 sr 1)

    Try

    For I = 2 To lLast - 1
    If IsNumeric(.Range("G1").Offset(I, 0).Value) Then
    dSum = dSum + CDbl(.Range("G1").Offset(I, 0).Value)
    End If
    Next I

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

    Re: Insert value into cell ... (2000 sr 1)

    Is correct?

    Option Explicit

    Public Sub InsertTotale()
    Dim lLast As Long, I As Long, dSum As Double
    With Worksheets("PERDITE")
    lLast = .Range("F65536").End(xlUp).Row
    dSum = 0
    For I = 2 To lLast - 1
    If IsNumeric(.Range("G1").Offset(I, 0).Value) Then
    dSum = dSum + CDbl(.Range("G1").Offset(I, 0).Value)
    End If
    Next I




    'For I = 2 To lLast - 1
    ' dSum = dSum + CDbl(.Range("G1").Offset(I, 0).Value)
    ' Next I



    .Range("F1").Offset(lLast, 0).Value = "TOTALE"
    .Range("G1").Offset(lLast, 0).Value = dSum
    End With
    End Sub

  12. #12
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert value into cell ... (2000 sr 1)

    Did it work? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Legare Coleman

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

    Re: Insert value into cell ... (2000 sr 1)

    work fine!

  14. #14
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert value into cell ... (2000 sr 1)

    Then I would guess that it is correct!
    Legare Coleman

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

    Re: Insert value into cell ... (2000 sr 1)

    Hans, i have insert the format font bold into macro, but...
    The old line formated in bold remain into cell... (see line 69) tath because the total in old elebaoration ended into line 68
    hoew to cancel the format before another import...

    Public Sub TOTALE_SALDI()
    Worksheets("SALDI").Select

    Dim lLast As Long, i As Long, dSum As Double
    With Worksheets("SALDI")
    lLast = .Range("F65536").End(xlUp).Row
    dSum = 0

    For i = 2 To lLast - 1
    If IsNumeric(.Range("G1").Offset(i, 0).Value) Then
    dSum = dSum + CDbl(.Range("G1").Offset(i, 0).Value)
    End If
    Next i

    .Range("F1").Offset(lLast, 0).Value = "TOTALE"
    .Range("F1").Offset(lLast, 0).Select
    Selection.Font.Bold = True

    .Range("G1").Offset(lLast, 0).Value = Format((dSum), "#,##0.00")
    .Range("G1").Offset(lLast, 0).Select
    Selection.Font.Bold = True
    End With
    Range("A3").Select

    End Sub

Page 1 of 2 12 LastLast

Posting Permissions

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