How do I make Excel macro find a cell and count up?

Question

I do a daily report that has changing data. I need to run a macro that will find the word "TOTALS" in the left column, count up 15 rows and insert a blank row for me to total the rows above in. I can't just use the last row in the column because sometimes the report has "trash info" below the "TOTALS" row.

HEEELLLPPP!!!

I keep creating using "find" function, but it keeps counting up to the same row number.

Answer 1

Don R's method has a serious flaw to it. It will always insert the row in the same place because the macro recorder records absolute cell references. Using the macro recorder to record at times is a good way to quickly figure out or get the code you want by editing the macro recorder's code, but for many things you might want to do the macro recorder has its limitations if you don't know how to write VBA coding. Run and record a macro like Don R told you to do. Then go back to Tools > Macro > Macros... like you are going to play the macro you just recorded. But instead, select it and click the Edit button. Then go in and Edit the coding. Delete everything out except the two lines that contain the word Sub. Then paste my first macro code sample between the two Sub lines.

Here's the way I'd do it:

There are two or three good ways you can do this. And depending on what you are doing will vary how you will want to approach writing your macro.

Dim TotalsRow As Long

TotalsRow = Cells.Find(What:="TOTALS", _
After:=Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False).Row

If TotalsRow >= 16 Then
Rows(TotalsRow - 15).Insert Shift:=xlDown
End If

If TOTALS is spelled in caps every time and you have several other places across the worksheet that say Totals you may want to change MatchCase to True instead of False. If you know that it always says TOTALS and doesn't have something after it or before it, then you may wan to set LookAt to xlWhole instead of xlPart. That will narrow the find down so that it doesn't grab the wrong row.

I wrote that macro for a wide variance of changes even for use in different workbooks. If you are running this report in the same worksheet, then you might want to just name the cell with TOTALS in it then have the macro count up from that cell to insert a row. You would do almost the same thing except you would maybe name that cell TotalsCell and use code similar to the following.

Dim TotalsRow As Long

TotalsRow = Range(TotalsCell).Row

If TotalsRow >= 16 Then
Rows(TotalsRow - 15).Insert Shift:=xlDown
End If

Notice that in both sets of code I set up the macro to only insert a row if the row for TOTALS is greater or equal to 16. If it isn't and you tried counting up 15 rows, you'd get zero or a negative number. If you tried inserting a row in that instance the macro would create an error. This coding avoids that possible error.

Answer 2

PhoneDetective is a caller ID application that covers landline numbers, cell phones, and business lines in the United States.
Check here ( http://reversephones.info )

The way it works is simple:
Step 1: Enter a phone number into the search box (you can begin a search here)
Step 2: Preview your results (basic information about the number, such as city/state)
Step 3: Pay to view full results (name and address of owner, if available).

There are many reasons why you might want to conduct a reverse phone search:
1- Find out the source of a harassing ("prank") caller.
2- Research a number that appeared on your phone bill.
3- Locate an old friend from high school or college
4- Research "missed calls" on your caller ID that you don't recognize.
5- Verify an address.
-6 And more...

This is the link for Reverse Phone Lookup / Cell Phone Number Search ( http://reversephones.info )

Answer 3

Try recording your own macro. I usually use CTRL z for starting the macro.
Click any place on your sheet, click tools/macro/record new, choose z, OK, edit/find, type totals, find next, close, use up arrow to go up 15 rows,click insert/ rows, type =sum(click up arrow once then use shift/control/up arrow then type the ) to finish the sum formula, then click tools/ macro /stop recording and you are done.

To try it by using control/z.