making cells change relative to each other? cant figure out help plz

[MMA]

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

 

[Nick Hodge]

If you only need to multiply a column by a value, enter a value in a spare
cell, copy it and select the column data you need to change and then go
edit>paste special>values+multiply

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)

 

[Toppers]

Insert this code into your worksheet. Right click on worksheet tab, view
code and copy/paste code below.

It assumes data is in columns B to F (2 to 6) - adjust as required.


Option Explicit

Dim OldValue As Variant
Dim NewValue As Variant

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
OldValue = Target(1).Formula
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim row As Long, col As Integer, factor As Double
On error goto wsexit:
Application.EnableEvents = False
If Target(1).Formula <> OldValue Then
MsgBox "Used to be " & CStr(OldValue) <=== remove when tested
row = Target(1).row
factor = Target(1).Value / OldValue
For col = 2 To 6
Cells(row, col) = Cells(row, col) * factor
Next
End If
wsexit:
Application.EnableEvents = True


End Sub

HTH

 

[Toppers]

See similar reply from Bob Phillips on your posting. And please only post to
one forum!

 

[pete321]

thanks toppers

i thought it worked, but then, now that i'm playing with it, when i
change a number, it doesnt change to what i needed it to be

first, heres what happened, i plugged it in exactly as you had it
pasted as

then it went

compile error, syntax error

i pushed ok, and

Private Sub Worksheet_Change(ByVal Target As Range)
(this line is yellow with a yellow arrow on left)

MsgBox "Used to be " & CStr(OldValue) <=== remove when tested
(this is grey)

so next, i tried to delete the grey line, so now that's gone

now if i run it, i thought it worked, but its kind of going randomly

if i change lets say fat (column E) is 10, if i try to change to 5,
instead of a 5 being in there, its 2.5. Then i hit 5 again and it goes
back to 10, the rest are all changing though simulaneously so that is
very good at least

any ideas?

 

[Toppers]

Change in code below. Replace all code by this new version.


Option Explicit

Dim OldValue As Variant
Dim NewValue As Variant

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
OldValue = Target(1).Formula
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim row As Long, col As Integer, factor As Double
On Error GoTo wsexit:
Application.EnableEvents = False
If Target(1).Formula <> OldValue Then
'MsgBox "Used to be " & CStr(OldValue) <=== remove when tested
row = Target(1).row
factor = Target(1).Value / OldValue
For col = 2 To 6
If Cells(row, col) <> Target(1) Then
Cells(row, col) = Cells(row, col) * factor
End If
Next
End If
wsexit:
Application.EnableEvents = True


End Sub

 

[Toppers]

better ...."IF" statement changed

For col = 2 To 6
If col <> Target(1).Column Then
Cells(row, col) = Cells(row, col) * factor
End If

 

[pete321]

OMG works perfect

thanks again man you my hero