Changing cells temp.

[fwday]

I am making a budgeting program. I have sales, I want to budget wages
and markdowns by dollor or pecent. If I have $100,000 dollars in sales
I want to have 2 cells for wages and 2 cells for Markdowns. Sometimes
I want to budget by percent and sometimes by dollar. The only problem
is that if I punch in a dollar amount it changes the formula in that
cell. And if I punch in percent it changes that cell. I want to have
the choice of budgeting by percent or dallars for bolth wages and
Markdowns. How can I make this happen.

Please break down to let me know what is happening in the code.

Also if there is a web sight I can go to to help me break it down would
be great.

 

[Tom Ogilvy]

Here is the answer I gave you two days ago and again yesterday.

Why don't you breakdown what isn't working for you. It was tested and works
fine as stated below.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo Errhandler
If Target.Count > 1 Then Exit Sub
If Target.Column = 2 Or Target.Column = 3 Then

If Len(Trim(Target.Value)) = 0 Then Exit Sub
Application.EnableEvents = False
If Target.Column = 2 And Target.Row Mod 2 = 0 Then
If IsNumeric(Target.Offset(-1, 0)) And _
Not IsEmpty(Target.Offset(-1, 0)) Then
If Target.Offset(-1, 0).Value <> 0 Then
Target.Offset(0, 1).Value = _
Target / Target.Offset(-1, 0)
End If
End If
ElseIf Target.Column = 3 And Target.Row Mod 2 = 0 Then
If IsNumeric(Target) Then
Target.Offset(0, -1) = Target.Offset(-1, -1) * _
Target
End If
End If
End If
Errhandler:
Application.EnableEvents = True
End Sub

This assumes your wages row is an even row and your entry will either be in
column B (dollars) or in Column C (percent).

The Sales row will always be an odd row.

So it works on all cells in columns B and C

 

[fwday]

Tom,
Below is the origanal code you sent me. It works great. now I want to
be able to use this with multible pairs of cells. That works for one
line such as wages. Now I want to use it for more catagories. People
have given me codes to add in to the original but It never works and
stops the function alltogether. I guess I don't know where to add the
lines in. Maybe you can help.

There is a copy of an example attached. The same one as before.

File Attached: http://www.excelforum.com/attachment.php?postid=333499 (example.xls)

 

[Tom Ogilvy]

I have given you completely revised code three times. All you have to do is
paste it into a new worksheet's code module and try it out. At the bottom I
explained what my assumptions were.

Reproduced here:
---------------------
This assumes your wages row is an even row and your entry will either be in
column B (dollars) or in Column C (percent).

The Sales row will always be an odd row.

So it works on all cells in columns B and C


Code is tested and works as stated.

 

[fwday]

Tom, I am sorry to keep bugging you. For some reson it is just not
working for me. I take the codes you send me and past them into the VB
on sheet 1 and the only thing I get is the first set of numbers to
work. This time I am adding in a copy of the actual form I am working
on so you can see my problem. I have been working with the example
that I origanaly sent you. If you have an e-mail address you want me
to send it to I can do that also.

File Attached: http://www.excelforum.com/attachment.php?postid=335476 (p & l panner renton oct. 2003.xls)

 

[Tom Ogilvy]

Sales 100
Wage 29 0.29
Sales 1000
Wage 500 0.5
Sales 200
Wage 25 0.125
Sales 200
Wage 66.666 0.33333


The first Sales was in E7.

I entered percents in C or Dollars in B in the even numbered rows and the
other column was calculated.

Copied the code right out of the email.

 

[Tom Ogilvy]

Here is code that works with your sample sheet:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo Errhandler
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("B7:C7")) Is Nothing Then
If Len(Trim(Target.Value)) = 0 Then Exit Sub
Application.EnableEvents = False
If Target.Address = "$B$7" Then
If IsNumeric(Target.Offset(-1, 0)) And _
Not IsEmpty(Target.Offset(-1, 0)) Then
If Target.Offset(-1, 0).Value <> 0 Then
Target.Offset(0, 1).Value = _
Target / Target.Offset(-1, 0)
End If
End If
Else
If IsNumeric(Target) Then
Target.Offset(0, -1) = Target.Offset(-1, -1) * _
Target
End If
End If
End If
If Not Intersect(Target, Range("E7,G7,I7,K7,M7,O7")) Is Nothing Then
If Len(Trim(Target.Value)) = 0 Then Exit Sub
Application.EnableEvents = False
If Not Intersect(Target, Range("E7,I7,M7")) Is Nothing Then
If IsNumeric(Target.Offset(-1, 0)) And _
Not IsEmpty(Target.Offset(-1, 0)) Then
If Target.Offset(-1, 0).Value <> 0 Then
Target.Offset(0, 2).Value = _
Target / Target.Offset(-1, 0)
End If
End If
Else
If IsNumeric(Target) Then
Target.Offset(0, -2) = Target.Offset(-1, -2) * _
Target
End If
End If
End If
Errhandler:
Application.EnableEvents = True
End Sub

 

[fwday]

Tom that works great for the provition for shrink line. But it is not
working for any of the other lines in colum b or c. I would like it to
work for all the lines that are not total lines.

 

[fwday]

Don't give up on me yet. I still would like to make this work