Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
making cells change relative to each other? cant figure it out
my question/problem is kind of weird but here goes. I'm praying
someone smart and nice with some vba knowledge can help I'm doing a food data worksheet for my nutrition class I have five columns per food quantity, calories. protein, carbs, fat the foods are also in rows What i want to do is enter the food data in for each food and then have it so later on if i want to change a value in any of the columns, the rest will move relative to the one for example, if 1 oz chicken breast is 46 calories, 1 gram fat, 0 carb, 9 protein. I can change the protein to be 25 (multiplied by 2.778) the rest will multipy their current values by 2.7778 automatically. I talked to some people and heard vba is probably the only way to do it and i am clueless when it comes to that. Any help with an appropriate script for what im trying to do would be much appreciated thanks in advance ---------------------------------------------- Posted with NewsLeecher v2.3 Final * Binary Usenet Leeching Made Easy * http://www.newsleecher.com/?usenet ---------------------------------------------- |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
making cells change relative to each other? cant figure it out
Private PrevVal As Double
'----------------------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) '----------------------------------------------------------------- Const WS_RANGE As String = "B:E" Dim i As Long On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target For i = 2 To 5 If i < .Column Then Me.Cells(.Row, i).Value = Me.Cells(.Row, i).Value * ..Value / PrevVal End If Next i End With End If ws_exit: Application.EnableEvents = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) PrevVal = Target.Value End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "MMA" wrote in message ng.com... my question/problem is kind of weird but here goes. I'm praying someone smart and nice with some vba knowledge can help I'm doing a food data worksheet for my nutrition class I have five columns per food quantity, calories. protein, carbs, fat the foods are also in rows What i want to do is enter the food data in for each food and then have it so later on if i want to change a value in any of the columns, the rest will move relative to the one for example, if 1 oz chicken breast is 46 calories, 1 gram fat, 0 carb, 9 protein. I can change the protein to be 25 (multiplied by 2.778) the rest will multipy their current values by 2.7778 automatically. I talked to some people and heard vba is probably the only way to do it and i am clueless when it comes to that. Any help with an appropriate script for what im trying to do would be much appreciated thanks in advance ---------------------------------------------- Posted with NewsLeecher v2.3 Final * Binary Usenet Leeching Made Easy * http://www.newsleecher.com/?usenet ---------------------------------------------- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change the right click menu on cells with imported data? | Excel Worksheet Functions | |||
checking that cells have a value before the workbook will close | Excel Worksheet Functions | |||
how to change absolute path to relative path | Excel Worksheet Functions | |||
% change in the last two cells in a row | Excel Worksheet Functions | |||
making cells adjust | Excel Discussion (Misc queries) |