VBA>>>>>VBA>>>>>MACRO>>>!!!!!Please Help me!!

[Excel_123]

I have created a template sheet and i am using that sheet extensively in my workbook. I have a macro written in that template sheet in its "Change" even procedure, so if any thing changes the macro should run. The thing i don't want code to run for any change i want it to run only for a specific change. For example, if any thing in column "K" ranges from rows 5 to 40 changes, it should take that change and add it into the old value of cell k42 of sheet called "sheet" ( BACKGROUND:the K column is fully based on the formula. Each cell in "K" column has a formula associated with it...For example K5=G5*(1+H5/100)*(1+I5/100)*(1+J5/100). K41 has the summ of all the values in cells K5 to K40...K41=sum(K5:K40)). So what i want is every time a value changes in any cell of "K" column, it should copy new value into the old value of cell K42 of the sheet called "Sheet". Some one suggested me the following code:

Public n As Long

Sub Worksheet_selectionChange(ByVal Target As Range)
On Error Resume Next
n = Target.value
End Sub

Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo 1 'Help w/ text and other possible entries
If Not Intersect(Target, [k5:k40]) Is Nothing Then
If Target.value <> "" Then
cell_contents = Target.value
Sheets("Sheet").[k42] = _
Sheets("Sheet").[k42].value + cell_contents
End If
If Target.value = "" Or Target.value = 0 Then
Sheets("Sheet").[k42].value = _
Sheets("Sheet").[k42].value - n
End If
End If
n = Target.value
1: End Sub

But It seems to me working for every change in the active sheet. Can anyone modify my code so that it could only work for what i have explained above..All thoughts would be appreciated.....I am a novice VBA programmer...Thanx in advance

[stevebausch]

May I make a suggestion?

IF you were to format your post as pseudo-code, one statement per line, it would be MUCH easier for you and others to translate it into code.

For example:
if any thing in column "K" ranges from rows 5 to 40 changes, Then
it should take that change and
add it into the old value of cell k42 of sheet called "sheet"
' the K column is fully based on the formula.
' Each cell in "K" column has a formula associated with it...
' For example K5=G5*(1+H5/100)*(1+I5/100)*(1+J5/100).
' K41 has the summ of all the values in cells K5 to K40...K41=sum(K5:K40)).


End If


Doing like this will help you to determine the clarity and accuracy of your logic, and may also help you solve the problem.

[NateO]

Howdy 1,2,3

Which are the specific cells that you manually change (that lead into the formulae?).

It seems that you just want the sum of the revised k5:k40 summation figure to overwrite k42 which would be:

sheets("Sheet").[k42] = [k41].value

In your worksheet event. Please advise.

Also, please keep all of these posts in one thread, if you post back with a question, the thread will be bumped to the top. Thanks,


Nate

[ This Message was edited by: NateO on 2002-07-03 19:24 ]

[Excel_123]

I am changing column "G" value manually that results into the value of K column after formulas manipulation.......Hope that helps you

[NateO]

Howdy 1,2,3

Hopefully when you said column G you meant column G of the worksheet that this event is going in (same as the one with the formulae). Try replacing the above with:

private Sub Worksheet_Change(ByVal Target As Range) 

If Not Intersect(Target, [g1:g65536]) Is Nothing Then _

sheets("Sheet").[k42].value = [k41].value 

End Sub

Follow the same procedure I mentioned earlier to get the code into the correct worksheet module. Change g1:g65536 to the range you're making the manual entries in. I haven't tested this, but am fairly sure this is what you're after.

It will update automatically. If you want to tie it to a button, call the procedure (which goes in a normal module) like the following:

private Sub Updt()

sheets("Sheet").[k42].value = sheets("mysheet").[k41].value 

End Sub

Change mysheet to the real name of your sheet. Also, if you're using a control button in a worksheet event, on a private sub routine, don't use call, use:

application.run "Updt"

Have a good one!


_________________
Cheers, Nate Oliver

[ This Message was edited by: NateO on 2002-07-03 20:39 ]

[Excel_123]

Hi there
Thank you very much, its working great. But now the problem is it only works for one sheet, it copies the sum of the K column into k42 cell of "sheet", but if i add another sheet, it replaces the old value of the k42 cell on sheet "sheet", which means i am loosing my old data from the previous sheet. Is there anyway instead of copying the k41 value, i just copy the respective K cell value. For example if some thing is changing lets suppose in G6, then instead of copying the k41 vlue, i just copy K6 value.....Thanks a lot again.....

[NateO]

Hi 1,2,3.

This is very easilly solved, I didn't define the sheet name for k41 as originally I was using worksheet event procedures.

If you tell me either the sheet name where the k41 sum exists or the physical number of it's location (index numebr), we can successfully wrap this up.

Glad to be of assistance, please keep these posts in one thread.

Edit: Actually I did provide a way to solve for this with the code above:

Change mysheet to the real name of your sheet

This will nail down the sheet you're working with.

_________________
Cheers, Nate Oliver


[ This Message was edited by: NateO on 2002-07-04 11:13 ]

[Excel_123]

HI
I am using worksheet even method. the code is given below:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, [g5:g40]) Is Nothing Then _
Sheets("Sheet").[k42].value = Sheets(ActiveSheet.Name).[k41].value
End Sub

Now it upgrades the value to cell k42 of Sheet "Sheet", but if i add another sheet based on the same template, instead of adding the values from new sheet to the old value of the k42 cell, it overwrites the value of the new sheet to cell k42. This event procedure is in my template sheet, so when ever i add that template sheet into my workbook,it updates the value from this sheet to "sheet"...but i don't want to loose the old value of the cell k42, after adding a new sheet. Secondly, it should work in the same way if delete any value from the cell of any active sheet. For example, if i have added two sheets based on that template, and lets say one sheet is filled up(all cells from G5 to G40)and then i add another sheet, it should not over write the value from the new sheet to cell k42 of "Sheet". Lets suppose i have total value of 10,000 in k42 cell after the end of one sheet, once i add another sheet (sheet2 based on that template) and start puttind data that data should be added into 10,000(old value of k42) not to be replaced. Hope it helps you...what i want and where am i standing...Thanx

[NateO]

Change:

ActiveSheet.Name to

the real name of the sheet you want, e.g.,

sheets("The Sheet That gets changed")

I'd say write a procedure to delete the event if it's not in the right sheet, but I think that's getting too complicated. Try the above.


Edit: also change [g5:g40] to:

sheets("The Correct Sheet Name Here").[g5:g40]


_________________
Cheers, Nate Oliver


[ This Message was edited by: NateO on 2002-07-04 12:15 ]

[tusharm]

I have a lot of questions, but no answers. Of course, there is no guarantee that answering these questions will lead to a solution to the original problem

What are you, the original poster, trying to do? Not in terms of XL formulas and macros, but in functional language? If you explain that, it might be possible to suggest a 'overall' solution rather than getting dragged deeper and deeper into a programming morass.

Apparently, there is a template. By definition, a template is something that will be used to create other 'actual' worksheets. This typically means that a template should be 'clean' -- with no dependencies on other sheets, and without other sheets depending on it.

In any case, you have a macro in the template sheet. That macro is supposed to do something with the contents of another sheet, named, rather poorly, IMO, 'Sheet.' What that something is remains unclear.

Why are you using a macro? If you want the contents of k5:k20 of the template summed in k42 of Sheet, why not put, in k42 of Sheet, the formula =sum(template!k5:k20)?

Assuming there is a reason you can't use a formula, what is the macro *supposed* to do? You've talked about using k41 of the template and putting that value in k42 of Sheet. You've also talked about summing/replacing the value of k5:k20 into k42. You've also talked about summing/replacing the contents based on another sheet created from the template.

What is this other sheet 'Sheet?' Is it a summary sheet? A temporary holding area? Something else?

Suppose you have something in G6 in the template sheet. Suppose that it has already been added to k42 of Sheet by the macro. Now, suppose the contents of G6 are deleted. Do you want what *was* in K6 subtracted from k42 of Sheet?

What if the user replaces the contents of G6 with another value? Do you want this to be treated as two operations? The first is deleting the existing contents, the second is adding a new value to G6?

If so, this is exactly what XL does by default, i.e., if you have a formula in Sheet!K42, it will happen automatically! If you want to do this programmatically, you will have to maintain a copy of the previous content of all related cells -- and reestablish them across a workbook close/open and/or system shutdown/failure.

If not, then you have an audit nightmare on your hands. Suppose you have 1000 in G6 and nothing else in G5:G20. So, Sheet!K42 will contain 1000. Now, you replace the 1000 in G6 with 2000. So, K42 will have 3000. Now, you delete the contents of G6. So, you will subtract 2000 from K42 and be left with 1000. However, there is no way of knowing why you have 1000 in K42 when K5:K20 are all empty. Unless you are a trying to "cook the books," this is a prescription for a disaster.

As I wrote at the beginning, I don't know why you are doing what you are trying to do. That makes it hard to suggest a functioning solution. Maybe if you shared your *intent*...