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
LadyGeek2 Posts 4 Registration date Thursday June 3, 2010 Status Member Last seen June 10, 2010 - Jun 10, 2010 at 12:13 PM
Related:
- EXCEL VBA HORIZONTAL ADDITION/LOOP
- Number to words in excel formula without vba - Guide
- Vba case like - Guide
- Snapchat horizontal - Guide
- How to open vba in excel mac - Guide
- Excel marksheet - Guide
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
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
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jun 9, 2010 at 02:30 PM
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
LadyGeek2
Posts
4
Registration date
Thursday June 3, 2010
Status
Member
Last seen
June 10, 2010
Jun 10, 2010 at 12:13 PM
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!
BTW, your copying column J and pasting back to J as values is way cool!
Jun 9, 2010 at 09:44 AM
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.
Jun 9, 2010 at 09:46 AM