CODEWITHSUNDEEP
excelvbscript
oakanb2
oakanb2

Reputation: 41

Subtotal VBScript

I have an excel spreadsheet like the one below (I made all confidential info white font so ignore the blank space)

enter image description here

The total column is in column d or the 4th column. What I need to do is subtotal the values in column d up until it gets to row that says "...-TOTAL". The issue I'm having is that after the row with "...-TOTAL" there are more rows that also need to be subtotaled up to "...-TOTAL" with "..." being the client's name. I've tried the code below but I think it's stuck in an infinite loop because instead of the script running 5 minutes and ending, it isn't ending at all. If you need more information please let me know. Thank you!

Do Until InStr(objExcel.Cells(c,2).Value, "TOTAL")
total = total + objExcel.Cells(e,4).Value
if InStr(objExcel.Cells(c,2).Value, "TOTAL") then 
    objExcel.Cells(c,4).Value = total
    total = 0
end if
Loop

Upvotes: 1

Views: 470

Answers (1)

JNevill
JNevill

Reputation: 50034

As I noted in my comment you should use a For Each loop here so you can check the value of "Total" in each Row's Column 2 (B). Something like:

'Loop through each cell in column B
For Each rngCell in Range("B:B").Cells
    'Check to see if it contains "TOTAL" as part of it's value
    If Instr(rngCell.value, "TOTAL") Then
        'Set the value of the cell 2 columns over (Column D) and exit loop
        rngCell.Offset(,2).value = total
        Exit For
    End If
    'Collect and increment the total
    total = total + rngCell.Offset(,2).value
Next

Upvotes: 2

Related Questions