Increase number of rows

[HiDbLevel]

As you can see in the attached file, I have two worksheets. The first one is an project estimating sheet. The second is a comparative sheet for evaluating the actual profit of a project. In the "Budget" sheet I may add or delete rows depending on the amount of product required for a project. The "Design" and "Expenses" sections never change.

My end goal is to create a macro that would move specific information from "Budget" to "Job Cost Tracking". The first step in this process is the only part I'm not sure of how to do. I would like the number of rows of both sheets to match. My first thought was to write a function that counts the number of rows then apply that number into a macro.

First of all, is that the most efficient approach? If not, what would you recommend? Secondly, if it is a good approach, how do you write a Count formula to count the number of rows between "Standard" and "Sub Contracted". I know how to Count if it's between dates, numbers, etc., but is there a way to Count between words?

Thanks for the help!

[JOHN H. DAVIS]

Here is one way to establish a row count.

Please Login or Register  to view this content.

[HiDbLevel]

Can I apply that code as part of an insert or autofill? In other words, rather than the code just telling me how many rows I have, can it use that number to regulate an AutoFill macro?

Thanks!

[JOHN H. DAVIS]

I'm sure it can be done. But you need to explain further what you need your requirements are.

[HiDbLevel]

Part of the code that you suggested includes...

Do Until ActiveCell.Value = "Standard"
ActiveCell.Offset(1).Select
Loop
Do Until ActiveCell.Value = "Sub Contracted"

Is there a way to apply that into a Range such that the AutoFill would go from the cell below Standard and stop at the cell above Sub Contracted.

My attempt at this failed miserably. The following macro keeps crashing excel.

Sub AutoFillTest()
Range("A6").Select
Do Until ActiveCell.Value = "Standard"
ActiveCell.Offset(1).Select
Loop
Selection.AutoFill Destination:=Range("A6"), Type:=xlFillDefault
Do Until ActiveCell.Value = "Sub Contracted"
Loop
End Sub

[JOHN H. DAVIS]

Not sure this is what you want?

Please Login or Register  to view this content.

This fills the Range("A6") value between "Standard" and "Sub Contracted"

[HiDbLevel]

That's close, but I have a formula in ("A6") and would like that formula to AutoFill. I'm certainly not a VBA expert but if I'm understanding your code correctly, can we just change the 10th line (ActiveCell.Value = Range("A6").Value) to something like ActiveCell.AutoFill Destination:=("J6"), Type:=FillDefault ?

I have tried this but I'm getting a run-time error, "AutoFill method of Range class failed".

Also, for some reason the Offset isn't moving the ActiveCell one row down from "Standard". In fact as I change the value it is still not moving meaning that the contents of ("A6") are replacing "Standard".

Thanks for your help on this

[JOHN H. DAVIS]

Sorry for the late reply. I was offline for a few days maybe:

Please Login or Register  to view this content.