Need to Automate Manual Formual for Adding Rows in Large Excel 2007 File

[alc]

Good Morning

I have a large worksheet with revenues/expenses of organizations. Each organization is represented by an identifying number and 5 or 6 rows of data. I need all organizations to be represented by 6 rows even if one row contains 0s. I have been using =COUNTIF(B:B,B2)=6 to screen for those organizations reporting 5 rows of data. When I get a false, I insert a row with a 0 in the column for revenues and expenses. This manual process has worked well on the state level where there are 30 or so organizations. On the national level, more than 2000+ organizations, I need to automate (1) identifying those with 5 rows and (2) inserting the 6th row.
Thank you for considering/responding to this request.

Al

 

[hippiehacker]

try

Sub test()
Application.ScreenUpdating = False
For sh = 1 To ActiveWorkbook.Sheets.Count
Sheets(sh).Activate
With ActiveSheet
    If Application.CountA(Cells(6, 1).EntireRow) = 0 Then
     Sheets(sh).Cells(6, 1).Value = 1
    End If
End With
Next sh

End Sub

 

[alc]

Thank you for your response. I am an newbie Excel user so would you please tell me where to insert the code?

Thanks again for taking the time to read/respond to my query.

Al

 

[hotsauce26]

In the excel window go to developer tab and click Visual Basic.

Then go to insert, module, and copy and paste the above code into the window.

To run the macro, go to view, macros, view macro and select the macro and click run

 

[hotsauce26]

Just remembered that you have to activate the developer tab.

go to file, excel options, popular, and select show developer tab in the ribbon

 

[alc]

Thank you - I'll let you know how I make out.

Al

 

[alc]

Hi

I ran the macro and, unfortunately, it did not work. I think I inserted it correctly (with the help of another forum member). When I ran the macro, it created another worksheet and placed a 1 in column A line 6.

Was I supposed to insert something into the macro?

Appreciate your thoughts.

Al

 

[hippiehacker]

just saw now that the data is all in one sheet it might more sense to us if we know how your sheet looks like and the data which is present

 

[alc]

Good Morning.

You are correct, the information is on one worksheet. I have 1 file for each year. Each file has 1 worksheet with 8 columns. The column titles are as follows:

A. Record #
B. Organization
C. Schedule
D. Line #
E. Column #
F. Value
G. Fiscal Year Begin Data
H. Fiscal Year End Date

Most of the companies report 6 lines of information: (1) revenue from services (2) expenses (3) profit/loss from services (4) additional revenue (5) additional expenses (6) net profit/loss. Some companies, however, exclude additional revenue which means that their data is contained in 5 rows. I have been using COUNTIF(B:B,B2)=6 to identify those companies with 5 rows of data. When I get a FALSE, 5 rows of data, I insert a 6th row so that the formula (revenue from services+additional revenue) - (expenses+additional expenses) = net profit/loss.

These files contain the data for 2000+ organizations which excludes, at least for me, doing this manually.

I don't think I can attach a worksheet example which I would gladly do.

Thank you for taking the time to respond to my query.