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
----------------------------------------------

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
editpaste specialvalues+multiply

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"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
----------------------------------------------

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

"Nick Hodge" wrote:

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
editpaste specialvalues+multiply

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"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
----------------------------------------------

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

"Toppers" wrote:

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

"Nick Hodge" wrote:

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
editpaste specialvalues+multiply

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"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
----------------------------------------------

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?


--
pete321
------------------------------------------------------------------------
pete321's Profile: http://www.excelforum.com/member.php...o&userid=35354
View this thread: http://www.excelforum.com/showthread...hreadid=551285

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

"pete321" wrote:


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?


--
pete321
------------------------------------------------------------------------
pete321's Profile: http://www.excelforum.com/member.php...o&userid=35354
View this thread: http://www.excelforum.com/showthread...hreadid=551285

better ...."IF" statement changed

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

"Toppers" wrote:

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

"pete321" wrote:


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?


--
pete321
------------------------------------------------------------------------
pete321's Profile: http://www.excelforum.com/member.php...o&userid=35354
View this thread: http://www.excelforum.com/showthread...hreadid=551285

OMG works perfect

thanks again man you my hero


--
pete321
------------------------------------------------------------------------
pete321's Profile: http://www.excelforum.com/member.php...o&userid=35354
View this thread: http://www.excelforum.com/showthread...hreadid=551285