Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Coding Question
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Coding Question
Can you post some sample rows of data? it is easier to get it right the
first times with actual data. "Matt" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Coding Question
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 -- HTH... Jim Thomlinson "Matt" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Coding Question
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 "Joel" wrote: Can you post some sample rows of data? it is easier to get it right the first times with actual data. "Matt" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro/VB coding question | Excel Discussion (Misc queries) | |||
Message Box Coding question | Excel Programming | |||
Coding question | Excel Programming | |||
Coding best practice question | Excel Programming | |||
coding question | Excel Programming |