VBA Coding Question

[Matt]

I'm trying to write some VBA code that will add formulas to specific rows in
an excel spreadsheet. The formulas will be determined by a code in a
seperate column. For example, current location is a cell in column "H":

If the value in column "D" = 2 then
sum all conitiguos cells above (akin to clicking on the summation symbol)
and
copy the formula to the last column to the right that has values then
move down
one row and repeat.

If the value in column "D" = 4 then
subtract the value one row above from the value two rows above and
copy the
formula to the last column to the right that has values then move down
one
row and repeat

If the value in column "D" = 6 then
Go to Home
EndIf

EndIf

EndIf


Any suggestions...?

Thanks

 

[Joel]

Can you post some sample rows of data? it is easier to get it right the
first times with actual data.

 

[Jim Thomlinson]

This is really generic but it should get you started...

dim rngToSearch as range
dim rng as range

with sheets("Sheet1")
set rngtosearch = .range(.range("D2"), .cells(rows.count, "D").end(xlUp))
end with

for each rng in rngtosearch
select case rng.value
Case 1,2
msgbox "Do This"
Case 3,4
msgbox "Do that"
Case Else
msgbox "Where am I and why am I in this handbasket"
End Select
next rng

 

[Matt]

Joel

Thanks for the reponse, see axample below

CostCenter PlaceHolder DataSource EmpNumber 2007/04 2007/05 2007/06 2007/07 2007/08 2007/09 2007/10 2007/11 2007/12
50004010 1 Actuals 00052139 0.025 0 0 0 0 0 0 0 0
50004010 1 Actuals 01000846 0 0 0.0875 0.55625 0.45 0 0 0 0
50004010 1 Actuals 01000847 0 0 0 0 0 0 0 0 0
50004010 1 Actuals 01000847 0 0 0 0 0 0 0 0 0
50004010 2 Actuals Sum above and to the right
50004010 3 Estimation 0.7 0.7 0.5 0.5 0.5 0.1 0.1 0.2 0.1
50004010 4 Estimation Subtract sum above from value directly above ( eg,
0.7 ) then copy across
50004010 5
50004020 1 Actuals 00053521 0.0875 0.175 0.16875 0.0375 0 0 0 0 0
50004020 1 Actuals 00053553 0.00625 0.1125 0.0125 0 0 0 0.0125 0 0
50004020 1 Actuals 00053553 0 0 0 0 0 0.03125 0.01875 0 0
50004020 1 Actuals 00095236 0 0 0 0 0 0.1 0 0 0
50004020 2 Actuals Sum above and to the right
50004020 3 Estimation 0.5 0 0 0 0 0 0 0 0.4
50004020 4 Estimation Subtract sum above from value directly above ( eg,
0.7 ) then copy across
50004020 5
6 END, return Home