locked
How to sum a dynamic column and put result in next empty cell using Excel through VB6 RRS feed

  • Question

  • High  All ,

    I have a VB6 application which automatically opens an excel sheet and copy data from textboxes to  column "H"

    so this column is dynamic ; sometimes it contains 2 Items and sometimes more than 10 items

    I have a code to chose the last empty cell in the column regardless its size :

     
    With Worksheets(CurrentSheetName).Range("H" & Rows.Count).End(xlUp).Offset(1, 0)
    .Formula = "=sum (??????)"
    
    End With
     

    So what should I write instead of (??????)  to get the sum of the dynamic column ?

     

    any suggestion is appreciated .

     

    N.B

    I tried this kind of code

    =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$AEmbarrassedA)-1,1)

    but I get error because ($) is not valid char in VB6



    • Edited by Alfidai Saturday, December 24, 2011 5:31 PM
    Saturday, December 24, 2011 5:19 PM

Answers

  • Here is the VBA (without OFFSET):

     

    Sub dural()
    Dim N As Long
    N = Cells(Rows.Count, "H").End(xlUp).Row
    Cells(N + 1, "H").Formula = "=SUM(H1:H" & N & ")"
    End Sub

     

     


    gsnu201111
    • Marked as answer by Alfidai Sunday, December 25, 2011 7:46 AM
    Saturday, December 24, 2011 6:47 PM

All replies

  • Here is the VBA (without OFFSET):

     

    Sub dural()
    Dim N As Long
    N = Cells(Rows.Count, "H").End(xlUp).Row
    Cells(N + 1, "H").Formula = "=SUM(H1:H" & N & ")"
    End Sub

     

     


    gsnu201111
    • Marked as answer by Alfidai Sunday, December 25, 2011 7:46 AM
    Saturday, December 24, 2011 6:47 PM
  • Thanks , Gary's Student

    I succeed when using clumn "H" 

    but when I try to change it o column "I" starting from "I12" to end of row it did not succeed

    what I do is :

    Sub dural()
    Dim N As Long
    N = Cells(Rows.Count, "I").End(xlUp).Row
    Cells(N + 1, "I").Formula = "=SUM(I12:I" & N & ")"
    End Sub

    Maybe I did something  wrong

    Thanks again and best regards


    You Never try, You never know
    Saturday, December 24, 2011 8:14 PM
  • Make sure there is data in column I from I12 downwards.
    gsnu201111
    Saturday, December 24, 2011 8:26 PM
  • Really genius ,

    during code execution I did forget to save temp data to column "I"

    so I use the save code

    Workbooks("MyWbook.xls").Save

    and put it before the previous code and succeeded

     

    thanks 4 your help

    Happy New Year !  ...  and have a nice Time !

    Alfidai


    You Never try, You never know
    • Edited by Alfidai Saturday, December 24, 2011 9:17 PM
    Saturday, December 24, 2011 9:01 PM
  • This post is extremely helpful.  Im looking, however, to take this capability one step further:

    I need to sum across a set of columns that may vary in length (i.e., a dataset with dollar amounts in columns 6 thru 12, and the number of rows vary with each query executed).  I have my next empty row of cells calculated (in variable formRow) and a loop to cycle through the columns (incrementing variable formCol).

    The challenge i face is converting formCol to the correct letter representing the column i am summing.  

    Looking to fix:

    "=SUM(" & formCol & "2:" & formCol & finalRow & ")"

    placed in a cell in row formRow under the data being summed.  Variable finalRow represents the last row of data.  How can i translate formCol into the correct corresponding letter (e.g., formCol=6 to the letter 'F')?

    Thank you in advance.


    • Edited by healthdba06 Friday, December 28, 2012 6:39 AM
    Friday, December 28, 2012 6:35 AM
  • In VBA, to convert a column number into column letters, use a UDF like:

    Public Function ColLetter(N As Long) As String
    Dim s As String
    s = Cells(1, N).Address
    ColLetter = Split(s, "$")(1)
    End Function


    gsnu201209

    Friday, December 28, 2012 12:40 PM