EXCEL VBA HORIZONTAL ADDITION/LOOP

Solved/Closed
LadyGeek2 Posts 4 Registration date Thursday June 3, 2010 Status Member Last seen June 10, 2010 - Jun 3, 2010 at 11:25 AM
LadyGeek2 Posts 4 Registration date Thursday June 3, 2010 Status Member Last seen June 10, 2010 - Jun 10, 2010 at 12:13 PM
Hello,

Horizontal addition thru last used call

OVERVIEW:

(1) Worksheet contains many columns. Numerical data always begins in row 5
(2) If there is data in AC, there will always be data in AD
(3) Sum cells AC&AD, put total in corresponding cells in J
(4) Addition loop to continue until last row with data has been used

PROBLEM:

(1) If AC5<>"" then Sum AC5+AD5 and put total in J5
(2) move to next row
(3) If AC/next row <>"" then Sum that cell and the adjacent cell, place total in corresponding cell in J (i.e., J6 = AC6+AD6, etc.)
(4) If AC = "", add border to last used call in J; move down one row; Sum J
(7) Delete columns AC & AD
(8) Do something to keep data in J from disappearing once columns AC & AD are deleted.

I am WAY over my head! Any help will be greatly appreciated. One way I tried was to used column K as a "holding" column for the addition, then copy those figures, paste as Values to J and proceed as outlined in step 4 above, but I was way over my head there, too.

XP / Excel 2003 VBA

THANK YOU!


Related:

2 responses

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jun 3, 2010 at 06:13 PM
Could you please upload a sample file with sample data etc on some shared site like https://authentification.site , http://wikisend.com/ , http://www.editgrid.com etc and post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too
1
LadyGeek2 Posts 4 Registration date Thursday June 3, 2010 Status Member Last seen June 10, 2010
Jun 9, 2010 at 09:44 AM
Okey dokey, here are the links to Excel files labeled Before and After. Thank you!

https://authentification.site/files/22872523/BEFORE.xls

https://authentification.site/files/22872524/AFTER.xls

There are a couple of instances in which I need to do the horizontal additon, but if I can learn how to do one, I can extrapolate and do the others.

Briefly, the formula would accomplish the following:

J5 = AC5+AD5
J6 = AC6+AD6
J7 = AC7+AD7

Etc.
I need to now how to have VBA tell the program to stop the addition - i.e., there is no more data to be added; to draw a line at the bottom of the last cell used; then move down to the next row and total J5 through the last used row. The EZ part is that data always begin with Row 5. There will be no empty cells from J5 through the last cell in that column with data. (There will be cells with 0.00) The difficult part for my pea-brain is that the number of rows used will vary.

Hope this clarifies things. Thanks again.
0
LadyGeek2 Posts 4 Registration date Thursday June 3, 2010 Status Member Last seen June 10, 2010
Jun 9, 2010 at 09:46 AM
P.S. I neglected to emphasize that, once the addition for Column J has been accomplished, all columns to the right of column M will be deleted, and I need to be sure that the numbers now in column J don't disappear with the deletion of those columns.
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jun 9, 2010 at 02:30 PM
Sub FixMe()
Dim Cell As Range
Dim lMaxRows As Long
Dim iMaxCols As Integer

    ' this is used to located the last row on the sheet
    Set Cell = Cells.Find("*", Cells(1, "A"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
    
    If Cell Is Nothing Then
       lMaxRows = 0
    
    Else
       lMaxRows = Cell.Row
    End If
    
    
    ' this is used to located the last used column on the sheet
    Set Cell = Cells.Find("*", Cells(1, "A"), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
    
    If Cell Is Nothing Then
       iMaxCols = 0
    
    Else
       iMaxCols = Cell.Column
    End If
    
    
    Set Cell = Nothing ' to destroy and free memory
    
    ' if the number of rows on the sheet is less than 5 then exit the routine
    If lMaxRows < 5 Then Exit Sub
    
    ' for each row starting from row 5 till last row of the sheet.
    With Range(Cells(5, "J"), Cells(lMaxRows, "J"))
    
         ' putting formula in J column as Sum(AC, AD). In formula RC29,
         ' R means same row where the formula is being written
         ' C29, means that 29th column of sheet (column AC)
        .FormulaR1C1 = "=SUM(RC29, RC30)"
        
        ' copying the column J
        .Copy
        
        ' pasting the values back on J as values to get rid of formula
        .PasteSpecial xlPasteValues
    
    End With
    
    'clearing every thing from column M till last column
    Range(Cells(5, "M"), Cells(lMaxRows, iMaxCols)).Clear
    
End Sub
0
LadyGeek2 Posts 4 Registration date Thursday June 3, 2010 Status Member Last seen June 10, 2010
Jun 10, 2010 at 12:13 PM
This is terrific! Thanks so much - now I'll print out your solution and study it so I really understand what is going on. Thank you so much - this has driven me to the funny farm. I may still wind up at the funny farm, but I'll be smarter. <g>

BTW, your copying column J and pasting back to J as values is way cool!
0