Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing cells temp.
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. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing cells temp.
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 -- Regards, Tom Ogilvy fwday wrote in message ... 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. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing cells temp.
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) ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing cells temp.
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 he --------------------- 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. --------------------- -- Regards, Tom Ogilvy fwday wrote in message ... 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) ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing cells temp.
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) ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing cells temp.
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. -- Regards, Tom Ogilvy "fwday" wrote in message ... 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) ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing cells temp.
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 -- Regards, Tom Ogilvy "fwday" wrote in message ... 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) ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing cells temp.
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. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing cells temp.
Don't give up on me yet. I still would like to make this work
------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding of temp file | Excel Discussion (Misc queries) | |||
Problem using obj.Namespace(sourceFileName) Namespace(DestinationFilename).Itemsand then opening the xls file which creates a Temp Folder inside Temp | Excel Discussion (Misc queries) | |||
Temp files remain | Excel Discussion (Misc queries) | |||
Temp files in Excel | Excel Discussion (Misc queries) | |||
Location of Temp File(s) - If any | Excel Discussion (Misc queries) |