w/ VBA Code
Posted by Fredrick on May 18, 2001 5:07 PM
Can someone tell me if it's possible with cell g28 (empty)
that when you enter a number in g28 it's multiplied
by a28 without returning a circular reference
Frederick
Posted by Capulet Mercutio on May 18, 2001 5:52 PM
If you want to have it done automatically every time a number is entered in G28, put the following in the Sheet module :-
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target = [G28] And IsNumeric([G28]) = True Then [G28].Value = [G28] * [A28]
Application.EnableEvents = True
End Sub
If you want to run it from a normal module :-
[G28].Value = [G28] * [A28]
Posted by Kevin James on May 18, 2001 7:01 PM
Question about your code
Hello Capulet,
While answering what questions I can to try to give back to this message-board community, I have also learned a great deal.
I tried the code you provided, placing it in Sheet1's code and it ran perfectly. Then I edited the code, changing A28 to A1 and G28 to B1.
I then deleted the row 28 and immediately got an error. I didn't think anything about it but afterward I was unable to get the code to run again. I had to completely close Excel and then relaunch the application and open the file again. I'm wondering if you can tell me what happened?
thanks,
Kevin
Posted by Fredrick on May 18, 2001 8:02 PM
Capulet,
Thank you for your help. Can the code be modified so
that after you've enter the number "200" in cell G28
and then "1.2" in A28 it returns 240, then if you
change A28 to "1.3" it returns 260 etc.?
Thanks again,Fredrick
Posted by CM on May 18, 2001 8:32 PM
Try this :-
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Dim rng As Range
Set rng = Range("A28, G28")
On Error Resume Next
If Not Intersect(rng, Target) Is Nothing And IsNumeric([A28]) And IsNumeric([G28]) Then [G28].Value = [G28] * [A28]
Application.EnableEvents = True
End Sub
Posted by CM on May 18, 2001 8:37 PM
Re: Question about your code
While answering what questions I can to try to give back to this message-board community, I have also learned a great deal. I tried the code you provided, placing it in Sheet1's code and it ran perfectly. Then I edited the code, changing A28 to A1 and G28 to B1. I then deleted the row 28 and immediately got an error. I didn't think anything about it but afterward I was unable to get the code to run again. I had to completely close Excel and then relaunch the application and open the file again. I'm wondering if you can tell me what happened? thanks, Kevin
I should have included an error handler :-
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error Resume Next
If Target = [G28] And IsNumeric([G28]) = True Then [G28].Value = [G28] * [A28]
Application.EnableEvents = True
End Sub
The reason you were not able to run the code again was probably that you clicked "End" on the error message box, which would have ended the macro before the last line was executed (i.e. EnableEvents would not have been set back to True)
Posted by Fredrick on May 18, 2001 9:18 PM
CM,
Still have a problem when i enter 200 in G28 and
then change A28 from 1.2(240)to 1.3 it multiplies
240*1.3(312)not 200 * 1.3 (260)Any ideas?
Thank you, Fredrick
Posted by CM on May 18, 2001 10:25 PM
Add a new worksheet and name it Sheet2. You can keep this sheet hidden if you wish.
Then check whether the following does what you need (put it in the sheet module where the entries are made, not in Sheet2's module) :-
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error Resume Next
If IsNumeric([A28]) And IsNumeric([G28]) Then
If Target = [G28] Then
Worksheets("Sheet2").[G28].Value = [G28]
[G28].Value = [G28] * [A28]
ElseIf Target = [A28] Then
[G28].Value = Worksheets("Sheet2").[G28] * [A28]
End If
End If
Application.EnableEvents = True
End Sub
Posted by Fredrick on May 18, 2001 11:00 PM
CM, Please read
CM,
Thanks for hanging in there w/ me. I'm not having
much luck with this. If it would help i could e-mail
you the workbook, if so please let me know and include
your e-mail address
Thanks, Jim
Posted by CM on May 18, 2001 11:26 PM
Re: CM, Please read
I've tested it and it works (unless I've misunderstood what you need).
Where is it not working?
What it does is to show in G28 the result of the number in A28 multiplied by whatever number was last input with the keyboard to G28. The result updates automatically for any new input to A28 or G28. CM, Thanks for hanging in there w/ me. I'm not having much luck with this. If it would help i could e-mail you the workbook, if so please let me know and include your e-mail address Thanks, Jim
Posted by Fredrick on May 18, 2001 11:41 PM
Re: CM, Please read #2
CM,
A28 is 1, G28 is 200, if i change A28 to 1.2 G28 should
be 240, likewise if i change A28 back to 1 G28 should
change back to 200. I wish i could explain this better
that's why i suggested e-mailing
Thanks for your help
Fredrick
I've tested it and it works (unless I've misunderstood what you need). Where is it not working? What it does is to show in G28 the result of the number in A28 multiplied by whatever number was last input with the keyboard to G28. The result updates automatically for any new input to A28 or G28. : CM, : Thanks for hanging in there w/ me. I'm not having : much luck with this. If it would help i could e-mail : you the workbook, if so please let me know and include : your e-mail address : Thanks, Jim
Posted by CM on May 19, 2001 12:02 AM
Re: CM, Please read #2
Yes, that is exactly what happens when I try it.
What happens when you try it?
CM, A28 is 1, G28 is 200, if i change A28 to 1.2 G28 should be 240, likewise if i change A28 back to 1 G28 should change back to 200. I wish i could explain this better that's why i suggested e-mailing Thanks for your help Fredrick
: I've tested it and it works (unless I've misunderstood what you need). : Where is it not working? : What it does is to show in G28 the result of the number in A28 multiplied by whatever number was last input with the keyboard to G28. The result updates automatically for any new input to A28 or G28.
Posted by Frederick on May 19, 2001 12:25 AM
Re: CM, Please read #3
CM,
Lets see if i can explain this more clearly.
After i enter a number "200" in G28 i need all
of the changes assending and dessending in G28
to be based on what i enter in A28. As i mentioned
once G28 "200" is multipied by A28 1.2 it becomes
"240" and will not change back to "200" if i enter
1 in A28, also once "200" is in G28 any number i enter
is multiplied by the last returned sum:
A28 G28
1.2 200 = 240 then if i enter 1.3 in A28 it multiplies
1.3 times 240 not the original number 200 so it returs
312
It should Calc like:
A28 G28
1.2 200 = 240
or
1.3 200 = 260
or
1 200 = 200
does it work like this on your end?
Thanks again
Fredrick
Yes, that is exactly what happens when I try it. What happens when you try it? : CM, : A28 is 1, G28 is 200, if i change A28 to 1.2 G28 should : be 240, likewise if i change A28 back to 1 G28 should : change back to 200. I wish i could explain this better : that's why i suggested e-mailing : Thanks for your help : Fredrick
Posted by Frederick on May 19, 2001 12:25 AM
Re: CM, Please read #3
CM,
Lets see if i can explain this more clearly.
After i enter a number "200" in G28 i need all
of the changes assending and dessending in G28
to be based on what i enter in A28. As i mentioned
once G28 "200" is multipied by A28 1.2 it becomes
"240" and will not change back to "200" if i enter
1 in A28, also once "200" is in G28 any number i enter
is multiplied by the last returned sum:
A28 G28
1.2 200 = 240 then if i enter 1.3 in A28 it multiplies
1.3 times 240 not the original number 200 so it returs
312
It should Calc like:
A28 G28
1.2 200 = 240
or
1.3 200 = 260
or
1 200 = 200
does it work like this on your end?
Thanks again
Fredrick
Yes, that is exactly what happens when I try it. What happens when you try it? : CM, : A28 is 1, G28 is 200, if i change A28 to 1.2 G28 should : be 240, likewise if i change A28 back to 1 G28 should : change back to 200. I wish i could explain this better : that's why i suggested e-mailing : Thanks for your help : Fredrick
Posted by CM on May 19, 2001 12:44 AM
Re: CM, Please read #3
It works exactly the way you want it.
Have you pasted the correct code to the Sheet module? (Also, do not put it in Sheet2).
Here it is again :-
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error Resume Next
If IsNumeric([A28]) And IsNumeric([G28]) Then
If Target = [G28] Then
Worksheets("Sheet2").[G28].Value = [G28]
[G28].Value = [G28] * [A28]
ElseIf Target = [A28] Then
[G28].Value = Worksheets("Sheet2").[G28] * [A28]
End If
End If
Application.EnableEvents = True
End Sub
If you still can't get it to work, I can either send you a sample workbook or you can send one to me at antonio@dasilva.com
CM, Lets see if i can explain this more clearly. After i enter a number "200" in G28 i need all of the changes assending and dessending in G28 to be based on what i enter in A28. As i mentioned once G28 "200" is multipied by A28 1.2 it becomes "240" and will not change back to "200" if i enter 1 in A28, also once "200" is in G28 any number i enter is multiplied by the last returned sum: A28 G28 1.2 200 = 240 then if i enter 1.3 in A28 it multiplies 1.3 times 240 not the original number 200 so it returs 312 It should Calc like: A28 G28 1.2 200 = 240 or 1.3 200 = 260 or 1 200 = 200 does it work like this on your end? Thanks again Fredrick Yes, that is exactly what happens when I try it. : What happens when you try it? :
Posted by CM on May 19, 2001 12:44 AM
Re: CM, Please read #3
It works exactly the way you want it.
Have you pasted the correct code to the Sheet module? (Also, do not put it in Sheet2).
Here it is again :-
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error Resume Next
If IsNumeric([A28]) And IsNumeric([G28]) Then
If Target = [G28] Then
Worksheets("Sheet2").[G28].Value = [G28]
[G28].Value = [G28] * [A28]
ElseIf Target = [A28] Then
[G28].Value = Worksheets("Sheet2").[G28] * [A28]
End If
End If
Application.EnableEvents = True
End Sub
If you still can't get it to work, I can either send you a sample workbook or you can send one to me at antonio@dasilva.com
CM, Lets see if i can explain this more clearly. After i enter a number "200" in G28 i need all of the changes assending and dessending in G28 to be based on what i enter in A28. As i mentioned once G28 "200" is multipied by A28 1.2 it becomes "240" and will not change back to "200" if i enter 1 in A28, also once "200" is in G28 any number i enter is multiplied by the last returned sum: A28 G28 1.2 200 = 240 then if i enter 1.3 in A28 it multiplies 1.3 times 240 not the original number 200 so it returs 312 It should Calc like: A28 G28 1.2 200 = 240 or 1.3 200 = 260 or 1 200 = 200 does it work like this on your end? Thanks again Fredrick Yes, that is exactly what happens when I try it. : What happens when you try it? :
Posted by Fredrick on May 19, 2001 1:05 AM
Re: CM, I'm e-mailing
Have you pasted the correct code to the Sheet module? (Also, do not put it in Sheet2). Here it is again :- Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If IsNumeric([A28]) And IsNumeric([G28]) Then If Target = [G28] Then Worksheets("Sheet2").[G28].Value = [G28] [G28].Value = [G28] * [A28] ElseIf Target = [A28] Then [G28].Value = Worksheets("Sheet2").[G28] * [A28] End If End If End Sub If you still can't get it to work, I can either send you a sample workbook or you can send one to me at antonio@dasilva.com
: CM, : Lets see if i can explain this more clearly. : After i enter a number "200" in G28 i need all : of the changes assending and dessending in G28 : to be based on what i enter in A28. As i mentioned : once G28 "200" is multipied by A28 1.2 it becomes : "240" and will not change back to "200" if i enter : 1 in A28, also once "200" is in G28 any number i enter : is multiplied by the last returned sum
Posted by Fredrick on May 19, 2001 1:05 AM
Re: CM, I'm e-mailing
Have you pasted the correct code to the Sheet module? (Also, do not put it in Sheet2). Here it is again :- Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If IsNumeric([A28]) And IsNumeric([G28]) Then If Target = [G28] Then Worksheets("Sheet2").[G28].Value = [G28] [G28].Value = [G28] * [A28] ElseIf Target = [A28] Then [G28].Value = Worksheets("Sheet2").[G28] * [A28] End If End If End Sub If you still can't get it to work, I can either send you a sample workbook or you can send one to me at antonio@dasilva.com
: CM, : Lets see if i can explain this more clearly. : After i enter a number "200" in G28 i need all : of the changes assending and dessending in G28 : to be based on what i enter in A28. As i mentioned : once G28 "200" is multipied by A28 1.2 it becomes : "240" and will not change back to "200" if i enter : 1 in A28, also once "200" is in G28 any number i enter : is multiplied by the last returned sum
