Contains Text

I am guessing, but are you trying to use the worksheet function FIND? (vs the vba method)

CBorders said:

If IsNumber(Find("Total", A1)) Then

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

Click to expand...

Find, at least the way you are trying to use it, is a worksheet function, not a VB function. The VB equivalent of what you are trying to do would use the InStr (in string) function....

If InStr(Range("A1").Value, "Total") > 0 Then

However, the way your code is structured, it won't work either way. You reference to A1 is absolute and doesn't change when you later on in your code select a cell offset by 1 from the last cell referenced in the loop. As it turns out, you won't need to fix anything as the range object has a Find method that can be used to iterate through all of the cells in Column A with the word "Total" in them. Unfortunately, I'm not crystal clear on what your code is supposed to be doing. Can you provide a written description of what your code is attempting to do?

GTO said:

I am guessing, but are you trying to use the worksheet function FIND? (vs the vba method)

Click to expand...

I honestly have no idea. I just started learning macro related stuff about a week ago. I'm trying to automate as much as I can of some reports I run and have gotten quite a bit, but I'm stuck on this step and none of the examples I've found online have worked or been helpful.

If you know what I need to put instead to trigger an if statement whenever the cell in column A contains the word Total, please let me know.

Rick Rothstein said:

Find, at least the way you are trying to use it, is a worksheet function, not a VB function. The VB equivalent of what you are trying to do would use the InStr (in string) function....

If InStr(Range("A1").Value, "Total") > 0 Then

However, the way your code is structured, it won't work either way. You reference to A1 is absolute and doesn't change when you later on in your code select a cell offset by 1 from the last cell referenced in the loop. As it turns out, you won't need to fix anything as the range object has a Find method that can be used to iterate through all of the cells in Column A with the word "Total" in them. Unfortunately, I'm not crystal clear on what your code is supposed to be doing. Can you provide a written description of what your code is attempting to do?

Click to expand...

Tried it with that and it doesn't have an error message now, but it never seems to trigger the condition. I'll try to give an example of what I want it to do.

So first I had to convert a list of times at locations to numbers and round up.
Original form was basically:

Location: 1h 08m
Location: 2h 05m
Total for Location: 3h 13m

Changed to:
Location Stop One: 1.25
Location Stop Two: 2.25
Total for Location: 3.25

The problem is I actually need it to add the two rounded up numbers and put it where the total is so that it reads:
Location Stop One: 1.25
Location Stop Two: 2.25
Total for Location: 3.5

So I'm trying to make a variable that takes the first stop at a location, adds it to the next stop, and the next stop, etc. until it reaches the line that says Total for (Location). At that point I want it to replace the current number at Total for Location, reset the variable, and move on to the next location until all locations have been gone through.

Okay. As Rick pointed out, I think a short/clear description of what we are wanting to do would be the best.

As we are starting in A1, it appears we want to do loop until we hit a blank cell. If that is correct, is there anything (data-wise and hopefully not) below these cells in Col A?

And what are we doing here: '...= Location And Location = 0'

Yes, I also have a question. Are you simply trying to find (one instance of) "Total" in column A? - Or will there be multiple instances found and you want to perform your code on each instance?
I suspect you're wanting to find multiple instances, so perhaps something like this?:
Is this even close to what you're really trying to do?

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?

HalfAce said:

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?

Click to expand...

Yes, that sounds right.

GTO said:

Okay. As Rick pointed out, I think a short/clear description of what we are wanting to do would be the best.

As we are starting in A1, it appears we want to do loop until we hit a blank cell. If that is correct, is there anything (data-wise and hopefully not) below these cells in Col A?

Click to expand...

No, there's nothing below the data in column A. After replacing my code with Rick's, the Loop runs and stops at the end where it's supposed to. But it's not stopping on the way to replace column H's number when the cell in column A contains total and it's not resetting the counter. So it's basically just summing all the numbers in column H and putting them at the end.

GTO said:

And what are we doing here: '...= Location And Location = 0'

Click to expand...

My amateurish attempt at trying to get the cell in column H to equal the value stored in the counter and then reset the counter to 0. It doesn't appear to ever get to this line of the code though.