Step 2: VBA Macro to Sum

[LearnVBA83]

Hi there,

I have a bunch of sheets in a tab that are all hardcoded. Is there a way to write VBA code to look for the word Expense in column B and add the sum functions for C - O?

Things i'm trying to avoid: Sometimes there isn't a number hardcoded which could impact the sum function in VBA. The number of rows in each category can also vary. I'm not great at VBA and formulas but it's almost like the macro would need to find expense, move one cell over, add a sum function upwards until it reaches the cell below the month, and do that for columns C-O each time you see the word expense in column B.

Just trying to replace those hardcoded expense totals with a formula. In the end i'll be trying to loop through a bunch of worksheets to do this with VBA. I'm just trying to do it in steps.

 

[offthelip]

try this code:

Sub test()
lastRow = Cells(Rows.Count, "B").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastRow, 15)) ' load all the dworksheet into memory
startrow = 2
For i = 2 To lastRow
 If inarr(i, 2) = "Expense" Then
  For j = 3 To 15
    tt = Chr(64 + j)
   Range(Cells(i, j), Cells(i, j)).Formula = "=Sum(" & tt & startrow & ":" & tt & i - 1 & ")"
    Next j
    startrow = i + 2
  End If
Next i
End Sub

 

[LearnVBA83]

Thank you! This seemed to work well. Two things: When i look at the first word "expense" it seems the formula went all the way to the top. First image below. When i look at the rest of the "expense" it seems the formula is including the month. Is there a way to fix that piece?

 

[offthelip]

When i look at the first word "expense" it seems the formula went all the way to the top.

Adjust this by changing this line to start on the corrct row:

startrow = 2

I failed to spot that you had pasted an image starting at row 5 so startrow should be 6 at the start.

When i look at the rest of the "expense" it seems the formula is including the month. Is there a way to fix that piece?

In your orginal picture you had two rows between the "sum" row and where the next lot of numbers start. In the second picture you have 3 rows so that . I had assumed that you always had 2 rows between each set of numbers, if it is 3 rows then you can adjust it by changing this line:

startrow = i + 2

to

startrow = i + 3

If the number of rows between each set of numbers varies that is something different and means more code to detect where the numbers start each time, this is possible if you need it

 

[LearnVBA83]

If the number of rows between each set of numbers varies that is something different and means more code to detect where the numbers start each time, this is possible if you need it

I think I would want it to detect where the numbers start each time since I don't have control over the initial creating of the data set so someone could add a row in and it may not be consistent each month.

Thank you so much! It's amazing what coding can do!

 

[offthelip]

try this code which detects the start of the set of number by checking column C to see whether the value is numeric and not blank

Sub test()
lastRow = Cells(Rows.Count, "B").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastRow, 15)) ' load all the dworksheet into memory
Startrow = ""
For i = 1 To lastRow
 If Startrow = "" Then
    If IsNumeric(inarr(i, 3)) And inarr(i, 3) <> "" Then
     Startrow = i
    End If
 Else
    If inarr(i, 2) = "Expense" Then
     For j = 3 To 15
       tt = Chr(64 + j)
      Range(Cells(i, j), Cells(i, j)).Formula = "=Sum(" & tt & Startrow & ":" & tt & i - 1 & ")"
     Next j
       Startrow = ""
    End If
 End If
Next i
End Sub

 

[LearnVBA83]

Hi there! Thanks again for helping me out here. I ran the macro on a couple worksheets and they vary. I think it's b/c the sheet isn't always standard. Let me know if you want me to load a spreadsheet using the XL2BB ad in in excel. I just learned about that yesterday!

1. Expense Total

2nd Expense Total worked!

3rd Expense Total didn't input a formula

4th Expense Total went too hight

5th Expense Total worked

 

[offthelip]

The problem is because the code is looking for numbers in the column B, so if there no numbers in column B obviously it doesn't work. Is there a column which will always have numbers in it? i.e. column O because it would be easy to change that, To detect which column has numbers makes it even more complicated. ( this is what I call "Requrements creep"!!)
Also the first "sum" includes the first row because there is a number in it!! If you want the code to detect where to start you must define a logical rule that will find the start row. If you can write down a rule it can be coded, I have just been guessing what your data looks like so not surprising it doesn't always work