Need Help with VBA code for an Excel Model Macro

[weston.perkins]

I am presently building a complex model in Excel and I would like to
create a macro that would insert a new line into a project cost
catagory. This line would then need to be inserted throughout the
rest of the model to be referenced. I have created a step by step
recorded macro as to the functionality and formating changes needed as
the new line item is placed throughout the workbook. The problem
occurs when selecting the bottom of a set catagory of costs. Instead
of referencing the bottom of the set each time the macro is run, it
continually inserts the line item on a specific row number. I do not
have enough knowledge of VBA and would appreciate any help that could
shed some light on fixing this problem. Below is a copy of the VBA
code of the recorded step by step macro.


Sub LandDevelopCostsAdd()
'
' LandDevelopCostsAdd Macro
' Macro recorded 3/20/2007 by wperkins
'

'
Rows("30:30").Select
Selection.Insert Shift:=xlDown
Rows("29:29").Select
Selection.Copy
Rows("30:30").Select
ActiveSheet.Paste
Sheets("Financing").Select
ActiveWindow.SmallScroll Down:=42
Rows("89:89").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown
Rows("97:97").Select
Selection.Insert Shift:=xlDown
ActiveWindow.SmallScroll Down:=-45
Rows("24:24").Select
Selection.Insert Shift:=xlDown
Range("F23").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Range("F24").Select
ActiveSheet.Paste
Range("A24:E24").Select
Range("E24").Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
ActiveWindow.SmallScroll Down:=54
Range("F98").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Delete Shift:=xlUp
ActiveWindow.SmallScroll Down:=96
Rows("187:187").Select
Selection.Insert Shift:=xlDown
Rows("194:194").Select
Selection.Insert Shift:=xlDown
ActiveWindow.SmallScroll Down:=-57
Rows("121:121").Select
Selection.Insert Shift:=xlDown
Range("F120").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Range("F121").Select
ActiveSheet.Paste
Range("A121:E121").Select
Range("E121").Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
ActiveWindow.SmallScroll Down:=57
Range("F195").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Delete Shift:=xlUp
ActiveWindow.SmallScroll Down:=99
Rows("285:285").Select
Selection.Insert Shift:=xlDown
Rows("291:291").Select
Selection.Insert Shift:=xlDown
ActiveWindow.SmallScroll Down:=-51
Rows("218:218").Select
Selection.Insert Shift:=xlDown
Range("F217").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Range("F218").Select
ActiveSheet.Paste
Range("A218:E218").Select
Range("E218").Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
ActiveWindow.SmallScroll Down:=54
Range("F292").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Delete Shift:=xlUp
ActiveWindow.SmallScroll Down:=87
Rows("383:383").Select
Selection.Insert Shift:=xlDown
Rows("388:388").Select
Selection.Insert Shift:=xlDown
ActiveWindow.SmallScroll Down:=-48
Rows("315:315").Select
Selection.Insert Shift:=xlDown
Range("F314").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Range("F315").Select
ActiveSheet.Paste
Range("A315:E315").Select
Range("E315").Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
ActiveWindow.SmallScroll Down:=54
Range("F389").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Delete Shift:=xlUp
ActiveWindow.SmallScroll Down:=96
Rows("481:481").Select
Selection.Insert Shift:=xlDown
ActiveWindow.SmallScroll Down:=9
Rows("493:493").Select
Selection.Insert Shift:=xlDown
ActiveWindow.SmallScroll Down:=-63
Rows("412:412").Select
Selection.Insert Shift:=xlDown
Range("F411").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Range("F412").Select
ActiveSheet.Paste
Range("A412:E412").Select
Range("E412").Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
ActiveWindow.SmallScroll Down:=60
Range("F489").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Delete Shift:=xlUp
ActiveWindow.SmallScroll Down:=-171
ActiveWindow.ScrollRow = 276
ActiveWindow.ScrollRow = 274
ActiveWindow.ScrollRow = 272
ActiveWindow.ScrollRow = 271
ActiveWindow.ScrollRow = 269
ActiveWindow.ScrollRow = 267
ActiveWindow.ScrollRow = 265
ActiveWindow.ScrollRow = 264
ActiveWindow.ScrollRow = 262
ActiveWindow.ScrollRow = 260
ActiveWindow.ScrollRow = 258
ActiveWindow.ScrollRow = 257
ActiveWindow.ScrollRow = 255
ActiveWindow.ScrollRow = 252
ActiveWindow.ScrollRow = 248
ActiveWindow.ScrollRow = 245
ActiveWindow.ScrollRow = 242
ActiveWindow.ScrollRow = 240
ActiveWindow.ScrollRow = 236
ActiveWindow.ScrollRow = 233
ActiveWindow.ScrollRow = 230
ActiveWindow.ScrollRow = 228
ActiveWindow.ScrollRow = 225
ActiveWindow.ScrollRow = 223
ActiveWindow.ScrollRow = 222
ActiveWindow.ScrollRow = 219
ActiveWindow.ScrollRow = 218
ActiveWindow.ScrollRow = 216
ActiveWindow.ScrollRow = 215
ActiveWindow.ScrollRow = 213
ActiveWindow.ScrollRow = 212
ActiveWindow.ScrollRow = 210
ActiveWindow.ScrollRow = 208
ActiveWindow.ScrollRow = 207
ActiveWindow.ScrollRow = 206
ActiveWindow.ScrollRow = 204
ActiveWindow.ScrollRow = 202
ActiveWindow.ScrollRow = 199
ActiveWindow.ScrollRow = 198
ActiveWindow.ScrollRow = 197
ActiveWindow.ScrollRow = 194
ActiveWindow.ScrollRow = 192
ActiveWindow.ScrollRow = 189
ActiveWindow.ScrollRow = 187
ActiveWindow.ScrollRow = 186
ActiveWindow.ScrollRow = 182
ActiveWindow.ScrollRow = 179
ActiveWindow.ScrollRow = 175
ActiveWindow.ScrollRow = 172
ActiveWindow.ScrollRow = 168
ActiveWindow.ScrollRow = 162
ActiveWindow.ScrollRow = 155
ActiveWindow.ScrollRow = 153
ActiveWindow.ScrollRow = 148
ActiveWindow.ScrollRow = 145
ActiveWindow.ScrollRow = 143
ActiveWindow.ScrollRow = 138
ActiveWindow.ScrollRow = 136
ActiveWindow.ScrollRow = 133
ActiveWindow.ScrollRow = 129
ActiveWindow.ScrollRow = 124
ActiveWindow.ScrollRow = 121
ActiveWindow.ScrollRow = 117
ActiveWindow.ScrollRow = 114
ActiveWindow.ScrollRow = 112
ActiveWindow.ScrollRow = 109
ActiveWindow.ScrollRow = 107
ActiveWindow.ScrollRow = 105
ActiveWindow.ScrollRow = 104
ActiveWindow.ScrollRow = 102
ActiveWindow.ScrollRow = 100
ActiveWindow.ScrollRow = 98
ActiveWindow.ScrollRow = 96
ActiveWindow.ScrollRow = 95
ActiveWindow.ScrollRow = 94
ActiveWindow.ScrollRow = 92
ActiveWindow.ScrollRow = 90
ActiveWindow.ScrollRow = 89
ActiveWindow.ScrollRow = 87
ActiveWindow.ScrollRow = 85
ActiveWindow.ScrollRow = 83
ActiveWindow.ScrollRow = 82
ActiveWindow.ScrollRow = 80
ActiveWindow.ScrollRow = 77
ActiveWindow.ScrollRow = 75
ActiveWindow.ScrollRow = 74
ActiveWindow.ScrollRow = 70
ActiveWindow.ScrollRow = 66
ActiveWindow.ScrollRow = 63
ActiveWindow.ScrollRow = 60
ActiveWindow.ScrollRow = 58
ActiveWindow.ScrollRow = 55
ActiveWindow.ScrollRow = 51
ActiveWindow.ScrollRow = 48
ActiveWindow.ScrollRow = 46
ActiveWindow.ScrollRow = 43
ActiveWindow.ScrollRow = 40
ActiveWindow.ScrollRow = 38
ActiveWindow.ScrollRow = 35
ActiveWindow.ScrollRow = 34
ActiveWindow.ScrollRow = 32
ActiveWindow.ScrollRow = 30
ActiveWindow.ScrollRow = 28
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 23
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
Sheets("Project Costs").Select
Range("B30").Select
End Sub

 

[Otto Moehrbach]

You have entirely too much code in your message for anyone to plow through
so I'm looking at just what you say. You say that the problem is selecting
or referencing the bottom of a column of numbers. The code line shown below
finds the last occupied row of Column A:
Set rLastRow = Range("A" & Rows.Count).End(xlUp)
tLastRow is a range and is the last occupied cell in Column A.
If you want the last row, just write:
LastRow=rLastRow.Row
What you didn't explain is where, relative to this last row that you just
found with the above code, do you want this new row inserted? HTH Otto

 

[weston.perkins]

The last row is relative to a group of costs. The reference is to
insert a line at the bottom of each group of costs. For example:
there is Land Acquisition Costs, Land Development Costs, Direct Costs,
etc. Under each of these is several lines of costs related to each
group of costs. The macro needs to select the bottom row of a group
(each group will have its own macro for inserting a line item) say
Land Acquisition costs. Here the macro needs to insert a a new line
copying the formating and formulas from the line above it then proceed
to a financing page to insert that line into its subsequent location
amongst 5 different loans. It would be easier to post the model to
visualize this, but I am unaware if this is possible. Unfortuantely,
I don't have subsequent knowledge of VBA, only of what I want the
macro to do. Is there a way to have someone write the code for me if
they were to see the model? If not, any help with understanding how
to write the VBA code to reference the last row of a group of items,
then subsequently copy that new line item throughout the entire model.

 

[Otto Moehrbach]

I understand a little more now about what you want. Here is what I want you
to do for me and I'll write the code for you. Pretend that I just walked
into your office off the street. I'm looking for a bathroom. In other
words, I know nothing about your business, your file, your data, nothing.
You need to explain to me, a total novice, how to find this last row of a
group of numbers within a larger group of numbers. Explain it in English,
not in VBA. What would you tell me? Don't use the language peculiar to
your business. Use generics only.
Let me give you an example. To determine the last row of numbers within
a larger group of numbers, I will need to know how to determine the start of
the NEXT group. How would you explain that?
I will be out-of-pocket until Monday. Perhaps someone else will jump
in, in the meantime. HTH Otto

 

[rwr]

The last row is relative to a group of costs. The reference is to
insert a line at the bottom of each group of costs. For example:
there is Land Acquisition Costs, Land Development Costs, Direct Costs,
etc. Under each of these is several lines of costs related to each
group of costs. The macro needs to select the bottom row of a group
(each group will have its own macro for inserting a line item) say
Land Acquisition costs. Here the macro needs to insert a a new line
copying the formating and formulas from the line above it then proceed
to a financing page to insert that line into its subsequent location
amongst 5 different loans. It would be easier to post the model to
visualize this, but I am unaware if this is possible. Unfortuantely,
I don't have subsequent knowledge of VBA, only of what I want the
macro to do. Is there a way to have someone write the code for me if
they were to see the model? If not, any help with understanding how
to write the VBA code to reference the last row of a group of items,
then subsequently copy that new line item throughout the entire model.

Not sure I can completely answer your question, but the problem is YOU
told excel by using the macro recorder where the last line is. Excel
will always insert the new line where you told it.

What you need is VBA code to loop thru your rows looking at the group
to, on-the-fly, find the last row of each group and then use that row
number to do the insert down.

Others better at VBA in Excel should be able to give you an example of
how to write this loop. I am not as strong in Word or Excel VBA as I am
in Access.

Hope this helps.

Ron

 

[weston.perkins]

Below is a rough breakout of what I am trying to accomplish. Say I
have a group of costs "Total Land Acquisition Costs" and I need to add
3 more lines between "Due Dilligence, Closing, Title" and the "Total
Land Acquisition Costs." I need the macro to select the bottom of the
group of costs for "Land Acquisition Costs" which is row #5 right now
and insert an entire line there. Each group of costs is separated by
its "Total amount," thus signigaling a break of costs. Then it needs
to copy the row above and paste that formating and cell formulas into
the newly inserted row. Each time you press the macro, it needs to
know the bottom of those costs, rather then select row #5, but rather
row #6 after pressing the macro for the second time and so forth and
always copy the row above its formating and formulas without adding
lines in a separate budget item like "Land Development Costs" or
always selecting a distinct row number. After that is complete, this
row then needs to filter throughout the model. If you can figure this
out first, then I can visualize the second step of how it needs to
filter out.

row

1 Budget Costs
2
3 Land Acquisition $50000
4 Due Diligence, Closing, Title $5000
5 --
6 Total Land Acquisition Costs $55000
7
8 Land Permits and Fees $5000
9 Land Dev- Internal $700
10 Land Architect & Engineer $3000
11 Land Site Indirects $0
12 Land Holding costs $0
13 Land Processing & Entitlement $0
14 Land Acquisition - Pili Mai Monthly
$0
15 --
16 Total Land Development Costs $8700