Contains Text

[CBorders]

I'm trying to write a macro that executes when the cell in column A contains the word Total. Looking online, I keep seeing examples that make me think Find should work, but every time I try to run it I am told that the Sub or Function is undefined.

My current code is:

Sub AddTotals()
'
' AddTotals Macro
' Add the Totals for each location
'

'

Dim Location As Currency


Range("A1").Select
Do While (IsEmpty(ActiveCell) = False Or IsEmpty(ActiveCell.Offset(1,0)) = False)
If IsNumber(Find("Total", A1)) Then
ActiveCell.Offset(0, 7).Value = Location And Location = 0
Else
Location = Location + ActiveCell.Offset(0, 7).Value
End If
ActiveCell.Offset(1, 0).Select

Loop

End Sub

Any assistance for why Find isn't working for me would be appreciated.

 

[CBorders]

I think the problem now is probably the reference. It may be checking A1 every time instead of updating the cell it needs to check for Total. But none of the alternatives I've tried have worked and I clearly don't know enough to figure out what would.

 

[GTO]

Okay, could you post some sample data with the expected results?

To post sample date:
Excel Jeanie
Or
Richard Schollar's HTML Maker
Or
Borders;Copy;Paste

 

[HalfAce]

Have you tried the code I posted last? If so, what were the results compared to what you want?

 

[CBorders]

Have you tried the code I posted last? If so, what were the results compared to what you want?

I made a macro with your code and it doesn't seem to be doing anything. :\ And it's a little more complicated than I can understand at the moment. I think Rick's InStr should work, but I think it may just be checking A1 over and over. Any ideas on how to just make InStr check the ActiveCell?

 

[HalfAce]

Originally Posted by HalfAce
So (after reading your latest post) am I right in thinking you want to go through column H and sum up all the values in the rows between the rows in column A that contain "Total", put that total in column H - same row labeled "Total" in column A, reset the counter to 0 and start summing from the next row again?

Yes, that sounds right.

My sample data before running the code:
A * H
dog 1 * 10
dog 2 * 10
Total * 10
dog 3 * 10
Total * 10
dog 4 * 10
dog 5 * 10
dog 6 * 10
dog 7 * 10
dog 8 * 10
Total * 10
dog 9 * 10
dog 10 * 10
dog 11 * 10
Total * 10


My data after running the code:
A * H
dog 1 * 10
dog 2 * 10
Total * $20.00
dog 3 * 10
Total * $10.00
dog 4 * 10
dog 5 * 10
dog 6 * 10
dog 7 * 10
dog 8 * 10
Total * $50.00
dog 9 * 10
dog 10 * 10
dog 11 * 10
Total * $30.00

The code I'm using:

Sub AddTotalsDemo()
Dim Location As Currency, LstRw As Long, Rng As Range
LstRw = Cells(Rows.Count, "A").End(xlUp).Row
For Each Rng In Range("A1:A" & LstRw)
  If Rng.Value = "Total" Then
    Rng(, 8).Value = Location
    Location = 0
  Else
    Location = Location + Rng(, 8).Value
  End If
Next Rng
End Sub

Is that not what you're looking to do? - or am I misunderstanding?

 

[Rick Rothstein]

Okay, I'm ready to take a guess (but it sure would be nice to see a "picture" of your layout). My guess is that the numbers you want to add are in Column H and that they are constants (not formulas); that there is one or more blank spaces between the sections of numbers that you want to total up; that each section of numbers that are to be added up are contiguous (that is, there are no empty cells within the section of data); and that the Total wording in Column A is located on the same row that the first empty cell under each section is on. If all those conditions are true, then this code should produce the totals you are looking for...

Sub FormTotals()
  Dim A As Range, Numbers As Range
  Set Numbers = Columns("H").SpecialCells(xlConstants)
  For Each A In Numbers.Areas
    A(1).Offset(A.Rows.Count).Value = WorksheetFunction.Sum(A)
  Next
End Sub

 

[HalfAce]

Yes, Seems Rick & I are guessing the same thing as far as what you're looking for.
The only difference I see in the two routines is that Rick's is looking for a blank cell in column A to input the sum in column H.
Mine only looks for the value "Total" in column A and returns the sum(s) in column H of the same row(s).

They're basically doing the same thing, just going about it a bit differently, so if we're both wrong, then I guess we really do need to see your actual layout.

Hope it (one or the other) helps.

 

[Rick Rothstein]

The only difference I see in the two routines is that Rick's is looking for a blank cell in column A to input the sum in column H.

Actually, my code never looks at Column A... it assumes a logical structure to the OP's layout and looks for the first blank under each grouping of numbers to be added and assumes if you look over at Column A, you will find the Total keyword.

Mine only looks for the value "Total" in column A and returns the sum(s) in column H of the same row(s).

I wasn't 100% sure if the word Total stood alone in its cell or if it had other text in there with it.

 

[CBorders]

Actually, my code never looks at Column A... it assumes a logical structure to the OP's layout and looks for the first blank under each grouping of numbers to be added and assumes if you look over at Column A, you will find the Total keyword.


I wasn't 100% sure if the word Total stood alone in its cell or if it had other text in there with it.

I'm going to try HalfAce's code again when I have a bit of time, but could you tell me how to upload a picture of the spreadsheet for you guys?

Also, I only made the variable currency because I wasn't sure what else would return something with two decimals. I'm not actually dealing with dollars though, so if there's another variable type that does that, I would rather use that.

 

[Rick Rothstein]

...but could you tell me how to upload a picture of the spreadsheet for you guys?

Reread GTO's post back at Message #12 (note that he has a typo in his second sentence... the word "date" should have been "data").