|
Please Macro or Formula Help needed
Situation:-
I have Data in column "G" and "H" (please see belwo) G H G68 3980.00 C47 -4000.00 ME4 -200.00 ME4 200.00 PG2 1200.00 I have "VLOOKUP" formula in column "B" that when ever I put value in any cell of column "A" then it should lookup that value in columns "G" and "H" and get value from 2nd column or column "H". Like if I put "G68" in any cell of column "A" then in column "B" formula should bring up value next to "G68" which will be "3980.00". Then in column "D" I have another formula which is "=B1-C1". Which gives balance when I put any value in coloumn "C". Like if I have value of "3980.00" in one of column "B" cells and when I put value of "1000.00" in next cell of coloumn "C" then I get balance of "2980.00" by formula in column "D". Macro needed:- I need a macro that when I get balance in column "D" as above mentioned "2980.00" and if I put "G68" again in any cell of column "A" then macro should bring up the balance in column "B" which was "2980.00". Because of the orginal budget was "3980.00" and I have taken "1000.00" as mentioned above and now I want macro to bring balance of budget if I put "G68" again in column "A". Please note that I mentioned "G68" just to explain the situation but it can be any value from column "G" (see table above) I hope you understood what I am trying to say can any one please help me in this. Thanks |
Please Macro or Formula Help needed
Hi,
I think I understand and don't believe you need a macro, try this instead A B C D G68 3980 1000 1980 G68 3980 1000 1980 Column A is a manually input value Column B formula =VLOOKUP(A1,$G$1:$H$5,2,FALSE) Column C is a manually input number Column D formula =VLOOKUP(A1,$G$1:$H$5,2,FALSE)-SUMPRODUCT(($A$1:$A$10=A1)*($C$1:$C$10)) The formula takes the original value and subtracts any manually input values. As you will note this only does the first 10 rows but yu can extend tha ranges to what you want. Mike "K" wrote: Situation:- I have Data in column "G" and "H" (please see belwo) G H G68 3980.00 C47 -4000.00 ME4 -200.00 ME4 200.00 PG2 1200.00 I have "VLOOKUP" formula in column "B" that when ever I put value in any cell of column "A" then it should lookup that value in columns "G" and "H" and get value from 2nd column or column "H". Like if I put "G68" in any cell of column "A" then in column "B" formula should bring up value next to "G68" which will be "3980.00". Then in column "D" I have another formula which is "=B1-C1". Which gives balance when I put any value in coloumn "C". Like if I have value of "3980.00" in one of column "B" cells and when I put value of "1000.00" in next cell of coloumn "C" then I get balance of "2980.00" by formula in column "D". Macro needed:- I need a macro that when I get balance in column "D" as above mentioned "2980.00" and if I put "G68" again in any cell of column "A" then macro should bring up the balance in column "B" which was "2980.00". Because of the orginal budget was "3980.00" and I have taken "1000.00" as mentioned above and now I want macro to bring balance of budget if I put "G68" again in column "A". Please note that I mentioned "G68" just to explain the situation but it can be any value from column "G" (see table above) I hope you understood what I am trying to say can any one please help me in this. Thanks |
Please Macro or Formula Help needed
On Dec 28, 8:55*am, Mike H wrote:
Hi, I think I understand and don't believe you need a macro, try this instead * A * * * * * * *B * * * * * * * *C * * * * * * D G68 * * 3980 * *1000 * *1980 G68 * * 3980 * *1000 * *1980 Column A is a manually input value Column B formula =VLOOKUP(A1,$G$1:$H$5,2,FALSE) Column C is a manually input number Column D formula =VLOOKUP(A1,$G$1:$H$5,2,FALSE)-SUMPRODUCT(($A$1:$A$10=A1)*($C$1:$C$10)) The formula takes the original value and subtracts any manually input values. As you will note this only does the first 10 rows but yu can extend tha ranges to what you want. Mike "K" wrote: Situation:- I have Data in column "G" and "H" (please see belwo) G * * * H G68 * * 3980.00 C47 * * -4000.00 ME4 * * -200.00 ME4 * * 200.00 PG2 * * 1200.00 I have "VLOOKUP" formula in column "B" that when ever I put value in any cell of column "A" then it should lookup that value in columns "G" and "H" and get value from 2nd column or column "H". Like if I put "G68" in any cell of column "A" then in column "B" formula should bring up value next to "G68" which will be "3980.00". Then in column "D" I have another formula which is "=B1-C1". Which gives balance when I put any value in coloumn "C". Like if I have value of "3980.00" in one of column "B" cells and when I put value of "1000.00" in next cell of coloumn "C" then I get balance of "2980.00" by formula in column "D". Macro needed:- I need a macro that when I get balance in column "D" as above mentioned "2980.00" and if I put "G68" again in any cell of column "A" then macro should bring up the balance in column "B" which was "2980.00". *Because of the orginal budget was "3980.00" and I have taken "1000.00" as mentioned above and now I want macro to bring balance of budget if I put "G68" again in column "A". Please note that I mentioned "G68" just to explain the situation but it can be any value from column "G" (see table above) I hope you understood what I am trying to say can any one please help me in this. *Thanks- Hide quoted text - - Show quoted text - Thanks for replying Mike. I want 1980 to come in column B when i enter G68 next time in column A. Actually what i am trying to do is that in code "G68" i have 3980 budget which come in coloumn B when i enter "G68" in coloumn A and when i put 1000 or any other figure in column C then in coloumn D i get balance that i have taken 1000 from 3980 budget but i want a formula that when i enter G68 again in column A then i want to have same Vlookup formula in column B but this time i want the balance figure which will be 3980 - 1000 = 2980 to appear in column B so i know that i got that much budget left to spend |
Please Macro or Formula Help needed
That's what my formula does. Put the 2 formula given in columns B & D and
then drag them down as far as required. For (say) project G68 every time you put G68 in column A and a value in column C you will get the remaining budget in column D Mike "K" wrote: On Dec 28, 8:55 am, Mike H wrote: Hi, I think I understand and don't believe you need a macro, try this instead A B C D G68 3980 1000 1980 G68 3980 1000 1980 Column A is a manually input value Column B formula =VLOOKUP(A1,$G$1:$H$5,2,FALSE) Column C is a manually input number Column D formula =VLOOKUP(A1,$G$1:$H$5,2,FALSE)-SUMPRODUCT(($A$1:$A$10=A1)*($C$1:$C$10)) The formula takes the original value and subtracts any manually input values. As you will note this only does the first 10 rows but yu can extend tha ranges to what you want. Mike "K" wrote: Situation:- I have Data in column "G" and "H" (please see belwo) G H G68 3980.00 C47 -4000.00 ME4 -200.00 ME4 200.00 PG2 1200.00 I have "VLOOKUP" formula in column "B" that when ever I put value in any cell of column "A" then it should lookup that value in columns "G" and "H" and get value from 2nd column or column "H". Like if I put "G68" in any cell of column "A" then in column "B" formula should bring up value next to "G68" which will be "3980.00". Then in column "D" I have another formula which is "=B1-C1". Which gives balance when I put any value in coloumn "C". Like if I have value of "3980.00" in one of column "B" cells and when I put value of "1000.00" in next cell of coloumn "C" then I get balance of "2980.00" by formula in column "D". Macro needed:- I need a macro that when I get balance in column "D" as above mentioned "2980.00" and if I put "G68" again in any cell of column "A" then macro should bring up the balance in column "B" which was "2980.00". Because of the orginal budget was "3980.00" and I have taken "1000.00" as mentioned above and now I want macro to bring balance of budget if I put "G68" again in column "A". Please note that I mentioned "G68" just to explain the situation but it can be any value from column "G" (see table above) I hope you understood what I am trying to say can any one please help me in this. Thanks- Hide quoted text - - Show quoted text - Thanks for replying Mike. I want 1980 to come in column B when i enter G68 next time in column A. Actually what i am trying to do is that in code "G68" i have 3980 budget which come in coloumn B when i enter "G68" in coloumn A and when i put 1000 or any other figure in column C then in coloumn D i get balance that i have taken 1000 from 3980 budget but i want a formula that when i enter G68 again in column A then i want to have same Vlookup formula in column B but this time i want the balance figure which will be 3980 - 1000 = 2980 to appear in column B so i know that i got that much budget left to spend |
Please Macro or Formula Help needed
On Dec 28, 9:43*am, Mike H wrote:
That's what my formula does. Put the 2 formula given in columns B & D and then drag them down as far as required. For (say) project G68 every time you put G68 in column A and a value in column C you will get the remaining budget in column D Mike "K" wrote: On Dec 28, 8:55 am, Mike H wrote: Hi, I think I understand and don't believe you need a macro, try this instead * A * * * * * * *B * * * * * * * *C * * * * * * D G68 * * 3980 * *1000 * *1980 G68 * * 3980 * *1000 * *1980 Column A is a manually input value Column B formula =VLOOKUP(A1,$G$1:$H$5,2,FALSE) Column C is a manually input number Column D formula =VLOOKUP(A1,$G$1:$H$5,2,FALSE)-SUMPRODUCT(($A$1:$A$10=A1)*($C$1:$C$10)) The formula takes the original value and subtracts any manually input values. As you will note this only does the first 10 rows but yu can extend tha ranges to what you want. Mike "K" wrote: Situation:- I have Data in column "G" and "H" (please see belwo) G * * * H G68 * * 3980.00 C47 * * -4000.00 ME4 * * -200.00 ME4 * * 200.00 PG2 * * 1200.00 I have "VLOOKUP" formula in column "B" that when ever I put value in any cell of column "A" then it should lookup that value in columns "G" and "H" and get value from 2nd column or column "H". Like if I put "G68" in any cell of column "A" then in column "B" formula should bring up value next to "G68" which will be "3980.00". Then in column "D" I have another formula which is "=B1-C1". Which gives balance when I put any value in coloumn "C". Like if I have value of "3980.00" in one of column "B" cells and when I put value of "1000.00" in next cell of coloumn "C" then I get balance of "2980.00" by formula in column "D". Macro needed:- I need a macro that when I get balance in column "D" as above mentioned "2980.00" and if I put "G68" again in any cell of column "A" then macro should bring up the balance in column "B" which was "2980.00". *Because of the orginal budget was "3980.00" and I have taken "1000.00" as mentioned above and now I want macro to bring balance of budget if I put "G68" again in column "A". Please note that I mentioned "G68" just to explain the situation but it can be any value from column "G" (see table above) I hope you understood what I am trying to say can any one please help me in this. *Thanks- Hide quoted text - - Show quoted text - Thanks for replying Mike. I want 1980 to come in column B when i enter G68 next time in column A. *Actually what i am trying to do is that in code "G68" i have 3980 budget which come in coloumn B when i enter "G68" in coloumn A and when i put 1000 or any other figure in column C then in coloumn D i get balance that i have taken 1000 from 3980 budget but i want a formula that when i enter G68 again in column A then i want to have same Vlookup formula in column B but this time i want the balance figure which will be 3980 - 1000 = 2980 to appear in column B so i know that i got that much budget left to spend- Hide quoted text - - Show quoted text - sorry for being pain Mike but i think may be i am still not be able to explain to you what i need. is there any way i can send you my spreadsheet and explain everything on that spreadsheet so you can understant better what i am trying to say |
Please Macro or Formula Help needed
Hi
Upload the file here and then post the link http://savefile.com/ Mike "K" wrote: On Dec 28, 9:43 am, Mike H wrote: That's what my formula does. Put the 2 formula given in columns B & D and then drag them down as far as required. For (say) project G68 every time you put G68 in column A and a value in column C you will get the remaining budget in column D Mike "K" wrote: On Dec 28, 8:55 am, Mike H wrote: Hi, I think I understand and don't believe you need a macro, try this instead A B C D G68 3980 1000 1980 G68 3980 1000 1980 Column A is a manually input value Column B formula =VLOOKUP(A1,$G$1:$H$5,2,FALSE) Column C is a manually input number Column D formula =VLOOKUP(A1,$G$1:$H$5,2,FALSE)-SUMPRODUCT(($A$1:$A$10=A1)*($C$1:$C$10)) The formula takes the original value and subtracts any manually input values. As you will note this only does the first 10 rows but yu can extend tha ranges to what you want. Mike "K" wrote: Situation:- I have Data in column "G" and "H" (please see belwo) G H G68 3980.00 C47 -4000.00 ME4 -200.00 ME4 200.00 PG2 1200.00 I have "VLOOKUP" formula in column "B" that when ever I put value in any cell of column "A" then it should lookup that value in columns "G" and "H" and get value from 2nd column or column "H". Like if I put "G68" in any cell of column "A" then in column "B" formula should bring up value next to "G68" which will be "3980.00". Then in column "D" I have another formula which is "=B1-C1". Which gives balance when I put any value in coloumn "C". Like if I have value of "3980.00" in one of column "B" cells and when I put value of "1000.00" in next cell of coloumn "C" then I get balance of "2980.00" by formula in column "D". Macro needed:- I need a macro that when I get balance in column "D" as above mentioned "2980.00" and if I put "G68" again in any cell of column "A" then macro should bring up the balance in column "B" which was "2980.00". Because of the orginal budget was "3980.00" and I have taken "1000.00" as mentioned above and now I want macro to bring balance of budget if I put "G68" again in column "A". Please note that I mentioned "G68" just to explain the situation but it can be any value from column "G" (see table above) I hope you understood what I am trying to say can any one please help me in this. Thanks- Hide quoted text - - Show quoted text - Thanks for replying Mike. I want 1980 to come in column B when i enter G68 next time in column A. Actually what i am trying to do is that in code "G68" i have 3980 budget which come in coloumn B when i enter "G68" in coloumn A and when i put 1000 or any other figure in column C then in coloumn D i get balance that i have taken 1000 from 3980 budget but i want a formula that when i enter G68 again in column A then i want to have same Vlookup formula in column B but this time i want the balance figure which will be 3980 - 1000 = 2980 to appear in column B so i know that i got that much budget left to spend- Hide quoted text - - Show quoted text - sorry for being pain Mike but i think may be i am still not be able to explain to you what i need. is there any way i can send you my spreadsheet and explain everything on that spreadsheet so you can understant better what i am trying to say |
Please Macro or Formula Help needed
On Dec 28, 10:01*am, Mike H wrote:
Hi Upload the file here and then post the link http://savefile.com/ Mike "K" wrote: On Dec 28, 9:43 am, Mike H wrote: That's what my formula does. Put the 2 formula given in columns B & D and then drag them down as far as required. For (say) project G68 every time you put G68 in column A and a value in column C you will get the remaining budget in column D Mike "K" wrote: On Dec 28, 8:55 am, Mike H wrote: Hi, I think I understand and don't believe you need a macro, try this instead * A * * * * * * *B * * * * * * * *C * * * * * * D G68 * * 3980 * *1000 * *1980 G68 * * 3980 * *1000 * *1980 Column A is a manually input value Column B formula =VLOOKUP(A1,$G$1:$H$5,2,FALSE) Column C is a manually input number Column D formula =VLOOKUP(A1,$G$1:$H$5,2,FALSE)-SUMPRODUCT(($A$1:$A$10=A1)*($C$1:$C$10)) The formula takes the original value and subtracts any manually input values. As you will note this only does the first 10 rows but yu can extend tha ranges to what you want. Mike "K" wrote: Situation:- I have Data in column "G" and "H" (please see belwo) G * * * H G68 * * 3980.00 C47 * * -4000.00 ME4 * * -200.00 ME4 * * 200.00 PG2 * * 1200.00 I have "VLOOKUP" formula in column "B" that when ever I put value in any cell of column "A" then it should lookup that value in columns "G" and "H" and get value from 2nd column or column "H". Like if I put "G68" in any cell of column "A" then in column "B" formula should bring up value next to "G68" which will be "3980.00". Then in column "D" I have another formula which is "=B1-C1". Which gives balance when I put any value in coloumn "C". Like if I have value of "3980.00" in one of column "B" cells and when I put value of "1000.00" in next cell of coloumn "C" then I get balance of "2980.00" by formula in column "D". Macro needed:- I need a macro that when I get balance in column "D" as above mentioned "2980.00" and if I put "G68" again in any cell of column "A" then macro should bring up the balance in column "B" which was "2980.00". *Because of the orginal budget was "3980.00" and I have taken "1000.00" as mentioned above and now I want macro to bring balance of budget if I put "G68" again in column "A". Please note that I mentioned "G68" just to explain the situation but it can be any value from column "G" (see table above) I hope you understood what I am trying to say can any one please help me in this. *Thanks- Hide quoted text - - Show quoted text - Thanks for replying Mike. I want 1980 to come in column B when i enter G68 next time in column A. *Actually what i am trying to do is that in code "G68" i have 3980 budget which come in coloumn B when i enter "G68" in coloumn A and when i put 1000 or any other figure in column C then in coloumn D i get balance that i have taken 1000 from 3980 budget but i want a formula that when i enter G68 again in column A then i want to have same Vlookup formula in column B but this time i want the balance figure which will be 3980 - 1000 = 2980 to appear in column B so i know that i got that much budget left to spend- Hide quoted text - - Show quoted text - sorry for being pain Mike but i think may be i am still not be able to explain to you what i need. *is there any way i can send you my spreadsheet and explain everything on that spreadsheet so you can understant better what i am trying to say- Hide quoted text - - Show quoted text - Hi mike please see the link below http://www.savefile.com/projects/808584589 If you want to link directly to the file: http://www.savefile.com/files/1288759 |
Please Macro or Formula Help needed
On Dec 28, 10:01*am, Mike H wrote:
Hi Upload the file here and then post the link http://savefile.com/ Mike "K" wrote: On Dec 28, 9:43 am, Mike H wrote: That's what my formula does. Put the 2 formula given in columns B & D and then drag them down as far as required. For (say) project G68 every time you put G68 in column A and a value in column C you will get the remaining budget in column D Mike "K" wrote: On Dec 28, 8:55 am, Mike H wrote: Hi, I think I understand and don't believe you need a macro, try this instead * A * * * * * * *B * * * * * * * *C * * * * * * D G68 * * 3980 * *1000 * *1980 G68 * * 3980 * *1000 * *1980 Column A is a manually input value Column B formula =VLOOKUP(A1,$G$1:$H$5,2,FALSE) Column C is a manually input number Column D formula =VLOOKUP(A1,$G$1:$H$5,2,FALSE)-SUMPRODUCT(($A$1:$A$10=A1)*($C$1:$C$10)) The formula takes the original value and subtracts any manually input values. As you will note this only does the first 10 rows but yu can extend tha ranges to what you want. Mike "K" wrote: Situation:- I have Data in column "G" and "H" (please see belwo) G * * * H G68 * * 3980.00 C47 * * -4000.00 ME4 * * -200.00 ME4 * * 200.00 PG2 * * 1200.00 I have "VLOOKUP" formula in column "B" that when ever I put value in any cell of column "A" then it should lookup that value in columns "G" and "H" and get value from 2nd column or column "H". Like if I put "G68" in any cell of column "A" then in column "B" formula should bring up value next to "G68" which will be "3980.00". Then in column "D" I have another formula which is "=B1-C1". Which gives balance when I put any value in coloumn "C". Like if I have value of "3980.00" in one of column "B" cells and when I put value of "1000.00" in next cell of coloumn "C" then I get balance of "2980.00" by formula in column "D". Macro needed:- I need a macro that when I get balance in column "D" as above mentioned "2980.00" and if I put "G68" again in any cell of column "A" then macro should bring up the balance in column "B" which was "2980.00". *Because of the orginal budget was "3980.00" and I have taken "1000.00" as mentioned above and now I want macro to bring balance of budget if I put "G68" again in column "A". Please note that I mentioned "G68" just to explain the situation but it can be any value from column "G" (see table above) I hope you understood what I am trying to say can any one please help me in this. *Thanks- Hide quoted text - - Show quoted text - Thanks for replying Mike. I want 1980 to come in column B when i enter G68 next time in column A. *Actually what i am trying to do is that in code "G68" i have 3980 budget which come in coloumn B when i enter "G68" in coloumn A and when i put 1000 or any other figure in column C then in coloumn D i get balance that i have taken 1000 from 3980 budget but i want a formula that when i enter G68 again in column A then i want to have same Vlookup formula in column B but this time i want the balance figure which will be 3980 - 1000 = 2980 to appear in column B so i know that i got that much budget left to spend- Hide quoted text - - Show quoted text - sorry for being pain Mike but i think may be i am still not be able to explain to you what i need. *is there any way i can send you my spreadsheet and explain everything on that spreadsheet so you can understant better what i am trying to say- Hide quoted text - - Show quoted text - Hi Mike have you receive my file |
Please Macro or Formula Help needed
Something has gone wrong with savefile and I cant post back so here's the
code. Right click the sheet tab, view code and paste this in. Delete your formulas in G5 - G20 Whenever you put a value in column D your remaining budget is updated in Column G Private Sub Worksheet_Change(ByVal Target As Range) Dim MyRange As Range Set MyRange = Range("D5:D20") If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub If Not Intersect(Target, Range("D5:D20")) Is Nothing Then If IsNumeric(Target) Then On Error Resume Next Application.EnableEvents = False budget = Target.Offset(0, 2).Value For Each c In MyRange If c.Offset(0, 1).Value = Target.Offset(0, 1).Value Then budget = budget + c.Value End If Next c Target.Offset(0, 3).Value = budget Application.EnableEvents = True On Error GoTo 0 End If End If End Sub Mike "K" wrote: On Dec 28, 10:01 am, Mike H wrote: Hi Upload the file here and then post the link http://savefile.com/ Mike "K" wrote: On Dec 28, 9:43 am, Mike H wrote: That's what my formula does. Put the 2 formula given in columns B & D and then drag them down as far as required. For (say) project G68 every time you put G68 in column A and a value in column C you will get the remaining budget in column D Mike "K" wrote: On Dec 28, 8:55 am, Mike H wrote: Hi, I think I understand and don't believe you need a macro, try this instead A B C D G68 3980 1000 1980 G68 3980 1000 1980 Column A is a manually input value Column B formula =VLOOKUP(A1,$G$1:$H$5,2,FALSE) Column C is a manually input number Column D formula =VLOOKUP(A1,$G$1:$H$5,2,FALSE)-SUMPRODUCT(($A$1:$A$10=A1)*($C$1:$C$10)) The formula takes the original value and subtracts any manually input values. As you will note this only does the first 10 rows but yu can extend tha ranges to what you want. Mike "K" wrote: Situation:- I have Data in column "G" and "H" (please see belwo) G H G68 3980.00 C47 -4000.00 ME4 -200.00 ME4 200.00 PG2 1200.00 I have "VLOOKUP" formula in column "B" that when ever I put value in any cell of column "A" then it should lookup that value in columns "G" and "H" and get value from 2nd column or column "H". Like if I put "G68" in any cell of column "A" then in column "B" formula should bring up value next to "G68" which will be "3980.00". Then in column "D" I have another formula which is "=B1-C1". Which gives balance when I put any value in coloumn "C". Like if I have value of "3980.00" in one of column "B" cells and when I put value of "1000.00" in next cell of coloumn "C" then I get balance of "2980.00" by formula in column "D". Macro needed:- I need a macro that when I get balance in column "D" as above mentioned "2980.00" and if I put "G68" again in any cell of column "A" then macro should bring up the balance in column "B" which was "2980.00". Because of the orginal budget was "3980.00" and I have taken "1000.00" as mentioned above and now I want macro to bring balance of budget if I put "G68" again in column "A". Please note that I mentioned "G68" just to explain the situation but it can be any value from column "G" (see table above) I hope you understood what I am trying to say can any one please help me in this. Thanks- Hide quoted text - - Show quoted text - Thanks for replying Mike. I want 1980 to come in column B when i enter G68 next time in column A. Actually what i am trying to do is that in code "G68" i have 3980 budget which come in coloumn B when i enter "G68" in coloumn A and when i put 1000 or any other figure in column C then in coloumn D i get balance that i have taken 1000 from 3980 budget but i want a formula that when i enter G68 again in column A then i want to have same Vlookup formula in column B but this time i want the balance figure which will be 3980 - 1000 = 2980 to appear in column B so i know that i got that much budget left to spend- Hide quoted text - - Show quoted text - sorry for being pain Mike but i think may be i am still not be able to explain to you what i need. is there any way i can send you my spreadsheet and explain everything on that spreadsheet so you can understant better what i am trying to say- Hide quoted text - - Show quoted text - Hi mike please see the link below http://www.savefile.com/projects/808584589 If you want to link directly to the file: http://www.savefile.com/files/1288759 |
Please Macro or Formula Help needed
Hi,
Still no luck with savefile so the file is here http://www.mediafire.com/?3m9nd0jojgs Mike "K" wrote: On Dec 28, 10:01 am, Mike H wrote: Hi Upload the file here and then post the link http://savefile.com/ Mike "K" wrote: On Dec 28, 9:43 am, Mike H wrote: That's what my formula does. Put the 2 formula given in columns B & D and then drag them down as far as required. For (say) project G68 every time you put G68 in column A and a value in column C you will get the remaining budget in column D Mike "K" wrote: On Dec 28, 8:55 am, Mike H wrote: Hi, I think I understand and don't believe you need a macro, try this instead A B C D G68 3980 1000 1980 G68 3980 1000 1980 Column A is a manually input value Column B formula =VLOOKUP(A1,$G$1:$H$5,2,FALSE) Column C is a manually input number Column D formula =VLOOKUP(A1,$G$1:$H$5,2,FALSE)-SUMPRODUCT(($A$1:$A$10=A1)*($C$1:$C$10)) The formula takes the original value and subtracts any manually input values. As you will note this only does the first 10 rows but yu can extend tha ranges to what you want. Mike "K" wrote: Situation:- I have Data in column "G" and "H" (please see belwo) G H G68 3980.00 C47 -4000.00 ME4 -200.00 ME4 200.00 PG2 1200.00 I have "VLOOKUP" formula in column "B" that when ever I put value in any cell of column "A" then it should lookup that value in columns "G" and "H" and get value from 2nd column or column "H". Like if I put "G68" in any cell of column "A" then in column "B" formula should bring up value next to "G68" which will be "3980.00". Then in column "D" I have another formula which is "=B1-C1". Which gives balance when I put any value in coloumn "C". Like if I have value of "3980.00" in one of column "B" cells and when I put value of "1000.00" in next cell of coloumn "C" then I get balance of "2980.00" by formula in column "D". Macro needed:- I need a macro that when I get balance in column "D" as above mentioned "2980.00" and if I put "G68" again in any cell of column "A" then macro should bring up the balance in column "B" which was "2980.00". Because of the orginal budget was "3980.00" and I have taken "1000.00" as mentioned above and now I want macro to bring balance of budget if I put "G68" again in column "A". Please note that I mentioned "G68" just to explain the situation but it can be any value from column "G" (see table above) I hope you understood what I am trying to say can any one please help me in this. Thanks- Hide quoted text - - Show quoted text - Thanks for replying Mike. I want 1980 to come in column B when i enter G68 next time in column A. Actually what i am trying to do is that in code "G68" i have 3980 budget which come in coloumn B when i enter "G68" in coloumn A and when i put 1000 or any other figure in column C then in coloumn D i get balance that i have taken 1000 from 3980 budget but i want a formula that when i enter G68 again in column A then i want to have same Vlookup formula in column B but this time i want the balance figure which will be 3980 - 1000 = 2980 to appear in column B so i know that i got that much budget left to spend- Hide quoted text - - Show quoted text - sorry for being pain Mike but i think may be i am still not be able to explain to you what i need. is there any way i can send you my spreadsheet and explain everything on that spreadsheet so you can understant better what i am trying to say- Hide quoted text - - Show quoted text - Hi Mike have you receive my file |
Please Macro or Formula Help needed
On Dec 28, 11:04*am, Mike H wrote:
Hi, Still no luck with savefile so the file is here http://www.mediafire.com/?3m9nd0jojgs Mike "K" wrote: On Dec 28, 10:01 am, Mike H wrote: Hi Upload the file here and then post the link http://savefile.com/ Mike "K" wrote: On Dec 28, 9:43 am, Mike H wrote: That's what my formula does. Put the 2 formula given in columns B & D and then drag them down as far as required. For (say) project G68 every time you put G68 in column A and a value in column C you will get the remaining budget in column D Mike "K" wrote: On Dec 28, 8:55 am, Mike H wrote: Hi, I think I understand and don't believe you need a macro, try this instead * A * * * * * * *B * * * * * * * *C * * * * * * D G68 * * 3980 * *1000 * *1980 G68 * * 3980 * *1000 * *1980 Column A is a manually input value Column B formula =VLOOKUP(A1,$G$1:$H$5,2,FALSE) Column C is a manually input number Column D formula =VLOOKUP(A1,$G$1:$H$5,2,FALSE)-SUMPRODUCT(($A$1:$A$10=A1)*($C$1:$C$10)) The formula takes the original value and subtracts any manually input values. As you will note this only does the first 10 rows but yu can extend tha ranges to what you want. Mike "K" wrote: Situation:- I have Data in column "G" and "H" (please see belwo) G * * * H G68 * * 3980.00 C47 * * -4000.00 ME4 * * -200.00 ME4 * * 200.00 PG2 * * 1200.00 I have "VLOOKUP" formula in column "B" that when ever I put value in any cell of column "A" then it should lookup that value in columns "G" and "H" and get value from 2nd column or column "H". Like if I put "G68" in any cell of column "A" then in column "B" formula should bring up value next to "G68" which will be "3980.00". Then in column "D" I have another formula which is "=B1-C1". Which gives balance when I put any value in coloumn "C". Like if I have value of "3980.00" in one of column "B" cells and when I put value of "1000.00" in next cell of coloumn "C" then I get balance of "2980.00" by formula in column "D". Macro needed:- I need a macro that when I get balance in column "D" as above mentioned "2980.00" and if I put "G68" again in any cell of column "A" then macro should bring up the balance in column "B" which was "2980.00". *Because of the orginal budget was "3980.00" and I have taken "1000.00" as mentioned above and now I want macro to bring balance of budget if I put "G68" again in column "A". Please note that I mentioned "G68" just to explain the situation but it can be any value from column "G" (see table above) I hope you understood what I am trying to say can any one please help me in this. *Thanks- Hide quoted text - - Show quoted text - Thanks for replying Mike. I want 1980 to come in column B when i enter G68 next time in column A. *Actually what i am trying to do is that in code "G68" i have 3980 budget which come in coloumn B when i enter "G68" in coloumn A and when i put 1000 or any other figure in column C then in coloumn D i get balance that i have taken 1000 from 3980 budget but i want a formula that when i enter G68 again in column A then i want to have same Vlookup formula in column B but this time i want the balance figure which will be 3980 - 1000 = 2980 to appear in column B so i know that i got that much budget left to spend- Hide quoted text - - Show quoted text - sorry for being pain Mike but i think may be i am still not be able to explain to you what i need. *is there any way i can send you my spreadsheet and explain everything on that spreadsheet so you can understant better what i am trying to say- Hide quoted text - - Show quoted text - Hi Mike have you receive my file- Hide quoted text - - Show quoted text - Thanks Mike thats very nice and this has solved my problem. I have just two qestions 1 - what line should i add into your macro that if i have no figure in column D and column F which are "Amount" and "Budget" columns then column G cell get blank or value should be ="". because at the moment if i delete every thing i can still see the balance amount. 2 - My second question is that i can see the remaing balance in column G but what if i want to see this in column F. Because i have deleted all formulas in column G but if i leave them there and in column F it lookup the main budget and if i have taken budget then it give me the balance budget in column F |
Please Macro or Formula Help needed
I'm pleased this answered your problem.
Q1, The code I gave you works by knowing which was the active cell when the code is called (the target) so if you select multiple cells and delete them the code doesn't know which is the target because several cells are selected. So to do what you ask would be a largish task and I woiuldn't have started from where I did. However, provided you delete cells in column D as a single cell then this should work. Private Sub Worksheet_Change(ByVal Target As Range) Dim MyRange As Range Set MyRange = Range("D5:D20") If Target.Cells.Count 1 Then Exit Sub If Not Intersect(Target, Range("D5:D20")) Is Nothing Then If IsNumeric(Target) Then On Error Resume Next Application.EnableEvents = False budget = Target.Offset(0, 2).Value For Each c In MyRange If c.Offset(0, 1).Value = Target.Offset(0, 1).Value Then budget = budget + c.Value End If Next c If Target.Value < 0 Then Target.Offset(0, 3).Value = budget Else Target.Offset(0, 3).Value = 0 End If Application.EnableEvents = True 'Allow run time errors again On Error GoTo 0 End If End If End Sub I don't understand the second question. Mike "K" wrote: On Dec 28, 11:04 am, Mike H wrote: Hi, Still no luck with savefile so the file is here http://www.mediafire.com/?3m9nd0jojgs Mike "K" wrote: On Dec 28, 10:01 am, Mike H wrote: Hi Upload the file here and then post the link http://savefile.com/ Mike "K" wrote: On Dec 28, 9:43 am, Mike H wrote: That's what my formula does. Put the 2 formula given in columns B & D and then drag them down as far as required. For (say) project G68 every time you put G68 in column A and a value in column C you will get the remaining budget in column D Mike "K" wrote: On Dec 28, 8:55 am, Mike H wrote: Hi, I think I understand and don't believe you need a macro, try this instead A B C D G68 3980 1000 1980 G68 3980 1000 1980 Column A is a manually input value Column B formula =VLOOKUP(A1,$G$1:$H$5,2,FALSE) Column C is a manually input number Column D formula =VLOOKUP(A1,$G$1:$H$5,2,FALSE)-SUMPRODUCT(($A$1:$A$10=A1)*($C$1:$C$10)) The formula takes the original value and subtracts any manually input values. As you will note this only does the first 10 rows but yu can extend tha ranges to what you want. Mike "K" wrote: Situation:- I have Data in column "G" and "H" (please see belwo) G H G68 3980.00 C47 -4000.00 ME4 -200.00 ME4 200.00 PG2 1200.00 I have "VLOOKUP" formula in column "B" that when ever I put value in any cell of column "A" then it should lookup that value in columns "G" and "H" and get value from 2nd column or column "H". Like if I put "G68" in any cell of column "A" then in column "B" formula should bring up value next to "G68" which will be "3980.00". Then in column "D" I have another formula which is "=B1-C1". Which gives balance when I put any value in coloumn "C". Like if I have value of "3980.00" in one of column "B" cells and when I put value of "1000.00" in next cell of coloumn "C" then I get balance of "2980.00" by formula in column "D". Macro needed:- I need a macro that when I get balance in column "D" as above mentioned "2980.00" and if I put "G68" again in any cell of column "A" then macro should bring up the balance in column "B" which was "2980.00". Because of the orginal budget was "3980.00" and I have taken "1000.00" as mentioned above and now I want macro to bring balance of budget if I put "G68" again in column "A". Please note that I mentioned "G68" just to explain the situation but it can be any value from column "G" (see table above) I hope you understood what I am trying to say can any one please help me in this. Thanks- Hide quoted text - - Show quoted text - Thanks for replying Mike. I want 1980 to come in column B when i enter G68 next time in column A. Actually what i am trying to do is that in code "G68" i have 3980 budget which come in coloumn B when i enter "G68" in coloumn A and when i put 1000 or any other figure in column C then in coloumn D i get balance that i have taken 1000 from 3980 budget but i want a formula that when i enter G68 again in column A then i want to have same Vlookup formula in column B but this time i want the balance figure which will be 3980 - 1000 = 2980 to appear in column B so i know that i got that much budget left to spend- Hide quoted text - - Show quoted text - sorry for being pain Mike but i think may be i am still not be able to explain to you what i need. is there any way i can send you my spreadsheet and explain everything on that spreadsheet so you can understant better what i am trying to say- Hide quoted text - - Show quoted text - Hi Mike have you receive my file- Hide quoted text - - Show quoted text - Thanks Mike thats very nice and this has solved my problem. I have just two qestions 1 - what line should i add into your macro that if i have no figure in column D and column F which are "Amount" and "Budget" columns then column G cell get blank or value should be ="". because at the moment if i delete every thing i can still see the balance amount. 2 - My second question is that i can see the remaing balance in column G but what if i want to see this in column F. Because i have deleted all formulas in column G but if i leave them there and in column F it lookup the main budget and if i have taken budget then it give me the balance budget in column F |
Please Macro or Formula Help needed
On Dec 28, 12:17*pm, Mike H wrote:
I'm pleased this answered your problem. Q1, The code I gave you works by knowing which was the active cell when the code is called (the target) so if you select multiple cells and delete them the code doesn't know which is the target because several cells are selected. So to do what you ask would be a largish task and I woiuldn't have started from where I did. However, provided you delete cells in column D as a single cell then this should work. Private Sub Worksheet_Change(ByVal Target As Range) Dim MyRange As Range Set MyRange = Range("D5:D20") * *If Target.Cells.Count 1 Then Exit Sub * * If Not Intersect(Target, Range("D5:D20")) Is Nothing Then * * * * If IsNumeric(Target) Then * * * * * * On Error Resume Next * * * * * * * * Application.EnableEvents = False * * * * * * budget = Target.Offset(0, 2).Value * *For Each c In MyRange * * * * * * If c.Offset(0, 1).Value = Target.Offset(0, 1).Value Then * * * * * * * * budget = budget + c.Value * * * * * * End If * * Next c * * * * * *If Target.Value < 0 Then * * * * * * * * Target.Offset(0, 3).Value = budget * * * * * * Else * * * * * * * * Target.Offset(0, 3).Value = 0 * * * * * * End If * * * * * * * * Application.EnableEvents = True * * * * * * 'Allow run time errors again * * * * * * On Error GoTo 0 * * * * End If * * End If End Sub I don't understand the second question. Mike "K" wrote: On Dec 28, 11:04 am, Mike H wrote: Hi, Still no luck with savefile so the file is here http://www.mediafire.com/?3m9nd0jojgs Mike "K" wrote: On Dec 28, 10:01 am, Mike H wrote: Hi Upload the file here and then post the link http://savefile.com/ Mike "K" wrote: On Dec 28, 9:43 am, Mike H wrote: That's what my formula does. Put the 2 formula given in columns B & D and then drag them down as far as required. For (say) project G68 every time you put G68 in column A and a value in column C you will get the remaining budget in column D Mike "K" wrote: On Dec 28, 8:55 am, Mike H wrote: Hi, I think I understand and don't believe you need a macro, try this instead * A * * * * * * *B * * * * * * * *C * * * * * * D G68 * * 3980 * *1000 * *1980 G68 * * 3980 * *1000 * *1980 Column A is a manually input value Column B formula =VLOOKUP(A1,$G$1:$H$5,2,FALSE) Column C is a manually input number Column D formula =VLOOKUP(A1,$G$1:$H$5,2,FALSE)-SUMPRODUCT(($A$1:$A$10=A1)*($C$1:$C$10)) The formula takes the original value and subtracts any manually input values. As you will note this only does the first 10 rows but yu can extend tha ranges to what you want. Mike "K" wrote: Situation:- I have Data in column "G" and "H" (please see belwo) G * * * H G68 * * 3980.00 C47 * * -4000.00 ME4 * * -200.00 ME4 * * 200.00 PG2 * * 1200.00 I have "VLOOKUP" formula in column "B" that when ever I put value in any cell of column "A" then it should lookup that value in columns "G" and "H" and get value from 2nd column or column "H". Like if I put "G68" in any cell of column "A" then in column "B" formula should bring up value next to "G68" which will be "3980.00". Then in column "D" I have another formula which is "=B1-C1".. Which gives balance when I put any value in coloumn "C". Like if I have value of "3980.00" in one of column "B" cells and when I put value of "1000.00" in next cell of coloumn "C" then I get balance of "2980.00" by formula in column "D". Macro needed:- I need a macro that when I get balance in column "D" as above mentioned "2980.00" and if I put "G68" again in any cell of column "A" then macro should bring up the balance in column "B" which was "2980.00". *Because of the orginal budget was "3980.00" and I have taken "1000.00" as mentioned above and now I want macro to bring balance of budget if I put "G68" again in column "A". Please note that I mentioned "G68" just to explain the situation but it can be any value from column "G" (see table above) I hope you understood what I am trying to say can any one please help me in this. *Thanks- Hide quoted text - - Show quoted text - Thanks for replying Mike. I want 1980 to come in column B when i enter G68 next time in column A. *Actually what i am trying to do is that in code "G68" i have 3980 budget which come in coloumn B when i enter "G68" in coloumn A and when i put 1000 or any other figure in column C then in coloumn D i get balance that i have taken 1000 from 3980 budget but i want a formula that when i enter G68 again in column A then i want to have same Vlookup formula in column B but this time i want the balance figure which will be 3980 - 1000 = 2980 to appear in column B so i know that i got that much budget left to spend- Hide quoted text - - Show quoted text - sorry for being pain Mike but i think may be i am still not be able to explain to you what i need. *is there any way i can send you my spreadsheet and explain everything on that spreadsheet so you can understant better what i am trying to say- Hide quoted text - - Show quoted text - Hi Mike have you receive my file- Hide quoted text - - Show quoted text - Thanks Mike thats very nice and this has solved my problem. I have just two qestions 1 - what line should i add into your macro that if i have no figure in column D and column F which are "Amount" and "Budget" columns then column G cell get blank or value should be ="". because at the moment if i delete every thing i can still see the balance amount. 2 - My second question is that i can see the remaing balance in column G but what if i want to see this in column F. Because i have deleted all formulas in column G but if i leave them there and in column F it lookup the main budget and if i have taken budget then it give me the balance budget in column F- Hide quoted text - - Show quoted text - Hi Mike thanks for replying for first question. Please see link below where i have uploded file which will explain you my second question. if you can solve my second question then this will completely solve my whole problem please do reply. http://www.savefile.com/files/1288928 |
Please Macro or Formula Help needed
Hvae a look if this does it for you
http://www.savefile.com/files/1289003 Mike "K" wrote: On Dec 28, 12:17 pm, Mike H wrote: I'm pleased this answered your problem. Q1, The code I gave you works by knowing which was the active cell when the code is called (the target) so if you select multiple cells and delete them the code doesn't know which is the target because several cells are selected. So to do what you ask would be a largish task and I woiuldn't have started from where I did. However, provided you delete cells in column D as a single cell then this should work. Private Sub Worksheet_Change(ByVal Target As Range) Dim MyRange As Range Set MyRange = Range("D5:D20") If Target.Cells.Count 1 Then Exit Sub If Not Intersect(Target, Range("D5:D20")) Is Nothing Then If IsNumeric(Target) Then On Error Resume Next Application.EnableEvents = False budget = Target.Offset(0, 2).Value For Each c In MyRange If c.Offset(0, 1).Value = Target.Offset(0, 1).Value Then budget = budget + c.Value End If Next c If Target.Value < 0 Then Target.Offset(0, 3).Value = budget Else Target.Offset(0, 3).Value = 0 End If Application.EnableEvents = True 'Allow run time errors again On Error GoTo 0 End If End If End Sub I don't understand the second question. Mike "K" wrote: On Dec 28, 11:04 am, Mike H wrote: Hi, Still no luck with savefile so the file is here http://www.mediafire.com/?3m9nd0jojgs Mike "K" wrote: On Dec 28, 10:01 am, Mike H wrote: Hi Upload the file here and then post the link http://savefile.com/ Mike "K" wrote: On Dec 28, 9:43 am, Mike H wrote: That's what my formula does. Put the 2 formula given in columns B & D and then drag them down as far as required. For (say) project G68 every time you put G68 in column A and a value in column C you will get the remaining budget in column D Mike "K" wrote: On Dec 28, 8:55 am, Mike H wrote: Hi, I think I understand and don't believe you need a macro, try this instead A B C D G68 3980 1000 1980 G68 3980 1000 1980 Column A is a manually input value Column B formula =VLOOKUP(A1,$G$1:$H$5,2,FALSE) Column C is a manually input number Column D formula =VLOOKUP(A1,$G$1:$H$5,2,FALSE)-SUMPRODUCT(($A$1:$A$10=A1)*($C$1:$C$10)) The formula takes the original value and subtracts any manually input values. As you will note this only does the first 10 rows but yu can extend tha ranges to what you want. Mike "K" wrote: Situation:- I have Data in column "G" and "H" (please see belwo) G H G68 3980.00 C47 -4000.00 ME4 -200.00 ME4 200.00 PG2 1200.00 I have "VLOOKUP" formula in column "B" that when ever I put value in any cell of column "A" then it should lookup that value in columns "G" and "H" and get value from 2nd column or column "H". Like if I put "G68" in any cell of column "A" then in column "B" formula should bring up value next to "G68" which will be "3980.00". Then in column "D" I have another formula which is "=B1-C1".. Which gives balance when I put any value in coloumn "C". Like if I have value of "3980.00" in one of column "B" cells and when I put value of "1000.00" in next cell of coloumn "C" then I get balance of "2980.00" by formula in column "D". Macro needed:- I need a macro that when I get balance in column "D" as above mentioned "2980.00" and if I put "G68" again in any cell of column "A" then macro should bring up the balance in column "B" which was "2980.00". Because of the orginal budget was "3980.00" and I have taken "1000.00" as mentioned above and now I want macro to bring balance of budget if I put "G68" again in column "A". Please note that I mentioned "G68" just to explain the situation but it can be any value from column "G" (see table above) I hope you understood what I am trying to say can any one please help me in this. Thanks- Hide quoted text - - Show quoted text - Thanks for replying Mike. I want 1980 to come in column B when i enter G68 next time in column A. Actually what i am trying to do is that in code "G68" i have 3980 budget which come in coloumn B when i enter "G68" in coloumn A and when i put 1000 or any other figure in column C then in coloumn D i get balance that i have taken 1000 from 3980 budget but i want a formula that when i enter G68 again in column A then i want to have same Vlookup formula in column B but this time i want the balance figure which will be 3980 - 1000 = 2980 to appear in column B so i know that i got that much budget left to spend- Hide quoted text - - Show quoted text - sorry for being pain Mike but i think may be i am still not be able to explain to you what i need. is there any way i can send you my spreadsheet and explain everything on that spreadsheet so you can understant better what i am trying to say- Hide quoted text - - Show quoted text - Hi Mike have you receive my file- Hide quoted text - - Show quoted text - Thanks Mike thats very nice and this has solved my problem. I have just two qestions 1 - what line should i add into your macro that if i have no figure in column D and column F which are "Amount" and "Budget" columns then column G cell get blank or value should be ="". because at the moment if i delete every thing i can still see the balance amount. 2 - My second question is that i can see the remaing balance in column G but what if i want to see this in column F. Because i have deleted all formulas in column G but if i leave them there and in column F it lookup the main budget and if i have taken budget then it give me the balance budget in column F- Hide quoted text - - Show quoted text - Hi Mike thanks for replying for first question. Please see link below where i have uploded file which will explain you my second question. if you can solve my second question then this will completely solve my whole problem please do reply. http://www.savefile.com/files/1288928 |
Please Macro or Formula Help needed
On Dec 28, 1:56 pm, Mike H wrote:
Hvae a look if this does it for you http://www.savefile.com/files/1289003 Mike "K" wrote: On Dec 28, 12:17 pm, Mike H wrote: I'm pleased this answered your problem. Q1, The code I gave you works by knowing which was the active cell when the code is called (the target) so if you select multiple cells and delete them the code doesn't know which is the target because several cells are selected. So to do what you ask would be a largish task and I woiuldn't have started from where I did. However, provided you delete cells in column D as a single cell then this should work. Private Sub Worksheet_Change(ByVal Target As Range) Dim MyRange As Range Set MyRange = Range("D5:D20") If Target.Cells.Count 1 Then Exit Sub If Not Intersect(Target, Range("D5:D20")) Is Nothing Then If IsNumeric(Target) Then On Error Resume Next Application.EnableEvents = False budget = Target.Offset(0, 2).Value For Each c In MyRange If c.Offset(0, 1).Value = Target.Offset(0, 1).Value Then budget = budget + c.Value End If Next c If Target.Value < 0 Then Target.Offset(0, 3).Value = budget Else Target.Offset(0, 3).Value = 0 End If Application.EnableEvents = True 'Allow run time errors again On Error GoTo 0 End If End If End Sub I don't understand the second question. Mike "K" wrote: On Dec 28, 11:04 am, Mike H wrote: Hi, Still no luck with savefile so the file is here http://www.mediafire.com/?3m9nd0jojgs Mike "K" wrote: On Dec 28, 10:01 am, Mike H wrote: Hi Upload the file here and then post the link http://savefile.com/ Mike "K" wrote: On Dec 28, 9:43 am, Mike H wrote: That's what my formula does. Put the 2 formula given in columns B & D and then drag them down as far as required. For (say) project G68 every time you put G68 in column A and a value in column C you will get the remaining budget in column D Mike "K" wrote: On Dec 28, 8:55 am, Mike H wrote: Hi, I think I understand and don't believe you need a macro, try this instead A B C D G68 3980 1000 1980 G68 3980 1000 1980 Column A is a manually input value Column B formula =VLOOKUP(A1,$G$1:$H$5,2,FALSE) Column C is a manually input number Column D formula =VLOOKUP(A1,$G$1:$H$5,2,FALSE)-SUMPRODUCT(($A$1:$A$10=A1)*($C$1:$C$10)) The formula takes the original value and subtracts any manually input values. As you will note this only does the first 10 rows but yu can extend tha ranges to what you want. Mike "K" wrote: Situation:- I have Data in column "G" and "H" (please see belwo) G H G68 3980.00 C47 -4000.00 ME4 -200.00 ME4 200.00 PG2 1200.00 I have "VLOOKUP" formula in column "B" that when ever I put value in any cell of column "A" then it should lookup that value in columns "G" and "H" and get value from 2nd column or column "H". Like if I put "G68" in any cell of column "A" then in column "B" formula should bring up value next to "G68" which will be "3980.00". Then in column "D" I have another formula which is "=B1-C1".. Which gives balance when I put any value in coloumn "C". Like if I have value of "3980.00" in one of column "B" cells and when I put value of "1000.00" in next cell of coloumn "C" then I get balance of "2980.00" by formula in column "D". Macro needed:- I need a macro that when I get balance in column "D" as above mentioned "2980.00" and if I put "G68" again in any cell of column "A" then macro should bring up the balance in column "B" which was "2980.00". Because of the orginal budget was "3980.00" and I have taken "1000.00" as mentioned above and now I want macro to bring balance of budget if I put "G68" again in column "A". Please note that I mentioned "G68" just to explain the situation but it can be any value from column "G" (see table above) I hope you understood what I am trying to say can any one please help me in this. Thanks- Hide quoted text - - Show quoted text - Thanks for replying Mike. I want 1980 to come in column B when i enter G68 next time in column A. Actually what i am trying to do is that in code "G68" i have 3980 budget which come in coloumn B when i enter "G68" in coloumn A and when i put 1000 or any other figure in column C then in coloumn D i get balance that i have taken 1000 from 3980 budget but i want a formula that when i enter G68 again in column A then i want to have same Vlookup formula in column B but this time i want the balance figure which will be 3980 - 1000 = 2980 to appear in column B so i know that i got that much budget left to spend- Hide quoted text - - Show quoted text - sorry for being pain Mike but i think may be i am still not be able to explain to you what i need. is there any way i can send you my spreadsheet and explain everything on that spreadsheet so you can understant better what i am trying to say- Hide quoted text - - Show quoted text - Hi Mike have you receive my file- Hide quoted text - - Show quoted text - Thanks Mike thats very nice and this has solved my problem. I have just two qestions 1 - what line should i add into your macro that if i have no figure in column D and column F which are "Amount" and "Budget" columns then column G cell get blank or value should be ="". because at the moment if i delete every thing i can still see the balance amount. 2 - My second question is that i can see the remaing balance in column G but what if i want to see this in column F. Because i have deleted all formulas in column G but if i leave them there and in column F it lookup the main budget and if i have taken budget then it give me the balance budget in column F- Hide quoted text - - Show quoted text - Hi Mike thanks for replying for first question. Please see link below where i have uploded file which will explain you my second question. if you can solve my second question then this will completely solve my whole problem please do reply. http://www.savefile.com/files/1288928- Hide quoted text - - Show quoted text - Thanks very much Mike you are great that's really works. I tried adding this into macro (please see below) If Target.Value < 0 Then Target.Offset(0, 2).Value = budget Else Target.Offset(0, 2).Value = "" End If so if I have nothing in column D or "Amount" column then column F cells should get blank but its not working am I doing right |
Please Macro or Formula Help needed
You need a couple of changes to do that, try this but remember if you select
multiple cells and delete the macro doesn't work because it doesn't know which is the target cell but that only matters if you delete anything in column D Private Sub Worksheet_Change(ByVal Target As Range) Dim MyRange As Range Set MyRange = Range("D5:D20") If Target.Cells.Count 1 Then Exit Sub If Not Intersect(Target, Range("D5:d20")) Is Nothing Then If IsNumeric(Target) Then On Error Resume Next Application.EnableEvents = False budget = WorksheetFunction.VLookup(Target.Offset(0, 1).Value, Range("j5:n20"), 5, False) For Each c In MyRange If c.Address < Target.Address Then If c.Offset(0, 1).Value = Target.Offset(0, 1).Value Then budget = budget + c.Value End If End If Next c If Target.Value < "" Then Target.Offset(0, 2).Value = budget Else Target.Offset(0, 2).Value = "" End If Application.EnableEvents = True On Error GoTo 0 End If End If End Sub "K" wrote: On Dec 28, 1:56 pm, Mike H wrote: Hvae a look if this does it for you http://www.savefile.com/files/1289003 Mike "K" wrote: On Dec 28, 12:17 pm, Mike H wrote: I'm pleased this answered your problem. Q1, The code I gave you works by knowing which was the active cell when the code is called (the target) so if you select multiple cells and delete them the code doesn't know which is the target because several cells are selected. So to do what you ask would be a largish task and I woiuldn't have started from where I did. However, provided you delete cells in column D as a single cell then this should work. Private Sub Worksheet_Change(ByVal Target As Range) Dim MyRange As Range Set MyRange = Range("D5:D20") If Target.Cells.Count 1 Then Exit Sub If Not Intersect(Target, Range("D5:D20")) Is Nothing Then If IsNumeric(Target) Then On Error Resume Next Application.EnableEvents = False budget = Target.Offset(0, 2).Value For Each c In MyRange If c.Offset(0, 1).Value = Target.Offset(0, 1).Value Then budget = budget + c.Value End If Next c If Target.Value < 0 Then Target.Offset(0, 3).Value = budget Else Target.Offset(0, 3).Value = 0 End If Application.EnableEvents = True 'Allow run time errors again On Error GoTo 0 End If End If End Sub I don't understand the second question. Mike "K" wrote: On Dec 28, 11:04 am, Mike H wrote: Hi, Still no luck with savefile so the file is here http://www.mediafire.com/?3m9nd0jojgs Mike "K" wrote: On Dec 28, 10:01 am, Mike H wrote: Hi Upload the file here and then post the link http://savefile.com/ Mike "K" wrote: On Dec 28, 9:43 am, Mike H wrote: That's what my formula does. Put the 2 formula given in columns B & D and then drag them down as far as required. For (say) project G68 every time you put G68 in column A and a value in column C you will get the remaining budget in column D Mike "K" wrote: On Dec 28, 8:55 am, Mike H wrote: Hi, I think I understand and don't believe you need a macro, try this instead A B C D G68 3980 1000 1980 G68 3980 1000 1980 Column A is a manually input value Column B formula =VLOOKUP(A1,$G$1:$H$5,2,FALSE) Column C is a manually input number Column D formula =VLOOKUP(A1,$G$1:$H$5,2,FALSE)-SUMPRODUCT(($A$1:$A$10=A1)*($C$1:$C$10)) The formula takes the original value and subtracts any manually input values. As you will note this only does the first 10 rows but yu can extend tha ranges to what you want. Mike "K" wrote: Situation:- I have Data in column "G" and "H" (please see belwo) G H G68 3980.00 C47 -4000.00 ME4 -200.00 ME4 200.00 PG2 1200.00 I have "VLOOKUP" formula in column "B" that when ever I put value in any cell of column "A" then it should lookup that value in columns "G" and "H" and get value from 2nd column or column "H". Like if I put "G68" in any cell of column "A" then in column "B" formula should bring up value next to "G68" which will be "3980.00". Then in column "D" I have another formula which is "=B1-C1".. Which gives balance when I put any value in coloumn "C". Like if I have value of "3980.00" in one of column "B" cells and when I put value of "1000.00" in next cell of coloumn "C" then I get balance of "2980.00" by formula in column "D". Macro needed:- I need a macro that when I get balance in column "D" as above mentioned "2980.00" and if I put "G68" again in any cell of column "A" then macro should bring up the balance in column "B" which was "2980.00". Because of the orginal budget was "3980.00" and I have taken "1000.00" as mentioned above and now I want macro to bring balance of budget if I put "G68" again in column "A". Please note that I mentioned "G68" just to explain the situation but it can be any value from column "G" (see table above) I hope you understood what I am trying to say can any one please help me in this. Thanks- Hide quoted text - - Show quoted text - Thanks for replying Mike. I want 1980 to come in column B when i enter G68 next time in column A. Actually what i am trying to do is that in code "G68" i have 3980 budget which come in coloumn B when i enter "G68" in coloumn A and when i put 1000 or any other figure in column C then in coloumn D i get balance that i have taken 1000 from 3980 budget but i want a formula that when i enter G68 again in column A then i want to have same Vlookup formula in column B but this time i want the balance figure which will be 3980 - 1000 = 2980 to appear in column B so i know that i got that much budget left to spend- Hide quoted text - - Show quoted text - sorry for being pain Mike but i think may be i am still not be able to explain to you what i need. is there any way i can send you my spreadsheet and explain everything on that spreadsheet so you can understant better what i am trying to say- Hide quoted text - - Show quoted text - Hi Mike have you receive my file- Hide quoted text - - Show quoted text - Thanks Mike thats very nice and this has solved my problem. I have just two qestions 1 - what line should i add into your macro that if i have no figure in column D and column F which are "Amount" and "Budget" columns then column G cell get blank or value should be ="". because at the moment if i delete every thing i can still see the balance amount. 2 - My second question is that i can see the remaing balance in column G but what if i want to see this in column F. Because i have deleted all formulas in column G but if i leave them there and in column F it lookup the main budget and if i have taken budget then it give me the balance budget in column F- Hide quoted text - - Show quoted text - Hi Mike thanks for replying for first question. Please see link below where i have uploded file which will explain you my second question. if you can solve my second question then this will completely solve my whole problem please do reply. http://www.savefile.com/files/1288928- Hide quoted text - - Show quoted text - Thanks very much Mike you are great that's really works. I tried adding this into macro (please see below) If Target.Value < 0 Then Target.Offset(0, 2).Value = budget Else Target.Offset(0, 2).Value = "" End If so if I have nothing in column D or "Amount" column then column F cells should get blank but its not working am I doing right |
Please Macro or Formula Help needed
On Dec 28, 2:44*pm, Mike H wrote:
You need a couple of changes to do that, try this but remember if you select multiple cells and delete the macro doesn't work because it doesn't know which is the target cell *but that only matters if you delete anything in column D Private Sub Worksheet_Change(ByVal Target As Range) Dim MyRange As Range Set MyRange = Range("D5:D20") * *If Target.Cells.Count 1 Then Exit Sub * * If Not Intersect(Target, Range("D5:d20")) Is Nothing Then * * * * If IsNumeric(Target) Then * * * * * * On Error Resume Next * * * * * * Application.EnableEvents = False * * * * * * budget = WorksheetFunction.VLookup(Target.Offset(0, 1).Value, Range("j5:n20"), 5, False) For Each c In MyRange * * If c.Address < Target.Address Then * * * * If c.Offset(0, 1).Value = Target.Offset(0, 1).Value Then * * * * * *budget = budget + c.Value * * * * End If * * End If Next c * * * * * *If Target.Value < "" Then * * * * * * * * Target.Offset(0, 2).Value = budget * * * * * * Else * * * * * *Target.Offset(0, 2).Value = "" * * * * * *End If * * * * * * * * * * * Application.EnableEvents = True * * * * * * * * On Error GoTo 0 * * * * End If * * End If End Sub "K" wrote: On Dec 28, 1:56 pm, Mike H wrote: Hvae a look if this does it for you http://www.savefile.com/files/1289003 Mike "K" wrote: On Dec 28, 12:17 pm, Mike H wrote: I'm pleased this answered your problem. Q1, The code I gave you works by knowing which was the active cell when the code is called (the target) so if you select multiple cells and delete them the code doesn't know which is the target because several cells are selected. So to do what you ask would be a largish task and I woiuldn't have started from where I did. However, provided you delete cells in column D as a single cell then this should work. Private Sub Worksheet_Change(ByVal Target As Range) Dim MyRange As Range Set MyRange = Range("D5:D20") * *If Target.Cells.Count 1 Then Exit Sub * * If Not Intersect(Target, Range("D5:D20")) Is Nothing Then * * * * If IsNumeric(Target) Then * * * * * * On Error Resume Next * * * * * * * * Application.EnableEvents = False * * * * * * budget = Target.Offset(0, 2).Value * *For Each c In MyRange * * * * * * If c.Offset(0, 1).Value = Target.Offset(0, 1).Value Then * * * * * * * * budget = budget + c.Value * * * * * * End If * * Next c * * * * * *If Target.Value < 0 Then * * * * * * * * Target.Offset(0, 3).Value = budget * * * * * * Else * * * * * * * * Target.Offset(0, 3).Value = 0 * * * * * * End If * * * * * * * * Application.EnableEvents = True * * * * * * 'Allow run time errors again * * * * * * On Error GoTo 0 * * * * End If * * End If End Sub I don't understand the second question. Mike "K" wrote: On Dec 28, 11:04 am, Mike H wrote: Hi, Still no luck with savefile so the file is here http://www.mediafire.com/?3m9nd0jojgs Mike "K" wrote: On Dec 28, 10:01 am, Mike H wrote: Hi Upload the file here and then post the link http://savefile.com/ Mike "K" wrote: On Dec 28, 9:43 am, Mike H wrote: That's what my formula does. Put the 2 formula given in columns B & D and then drag them down as far as required. For (say) project G68 every time you put G68 in column A and a value in column C you will get the remaining budget in column D Mike "K" wrote: On Dec 28, 8:55 am, Mike H wrote: Hi, I think I understand and don't believe you need a macro, try this instead * A * * * * * * *B * * * * * * * *C * * * * * * D G68 * * 3980 * *1000 * *1980 G68 * * 3980 * *1000 * *1980 Column A is a manually input value Column B formula =VLOOKUP(A1,$G$1:$H$5,2,FALSE) Column C is a manually input number Column D formula =VLOOKUP(A1,$G$1:$H$5,2,FALSE)-SUMPRODUCT(($A$1:$A$10=A1)*($C$1:$C$10)) The formula takes the original value and subtracts any manually input values. As you will note this only does the first 10 rows but yu can extend tha ranges to what you want. Mike "K" wrote: Situation:- I have Data in column "G" and "H" (please see belwo) G * * * H G68 * * 3980.00 C47 * * -4000.00 ME4 * * -200.00 ME4 * * 200.00 PG2 * * 1200.00 I have "VLOOKUP" formula in column "B" that when ever I put value in any cell of column "A" then it should lookup that value in columns "G" and "H" and get value from 2nd column or column "H". Like if I put "G68" in any cell of column "A" then in column "B" formula should bring up value next to "G68" which will be "3980.00". Then in column "D" I have another formula which is "=B1-C1".. Which gives balance when I put any value in coloumn "C". Like if I have value of "3980.00" in one of column "B" cells and when I put value of "1000.00" in next cell of coloumn "C" then I get balance of "2980.00" by formula in column "D". Macro needed:- I need a macro that when I get balance in column "D" as above mentioned "2980.00" and if I put "G68" again in any cell of column "A" then macro should bring up the balance in column "B" which was "2980.00". *Because of the orginal budget was "3980.00" and I have taken "1000.00" as mentioned above and now I want macro to bring balance of budget if I put "G68" again in column "A". Please note that I mentioned "G68" just to explain the situation but it can be any value from column "G" (see table above) I hope you understood what I am trying to say can any one please help me in this. *Thanks- Hide quoted text - - Show quoted text - Thanks for replying Mike. I want 1980 to come in column B when i enter G68 next time in column A. *Actually what i am trying to do is that in code "G68" i have 3980 budget which come in coloumn B when i enter "G68" in coloumn A and when i put 1000 or any other figure in column C then in coloumn D i get balance that i have taken 1000 from 3980 budget but i want a formula that when i enter G68 again in column A then i want to have same Vlookup formula in column B but this time i want the balance figure which will be 3980 - 1000 = 2980 to appear in column B so i know that i got that much budget left to spend- Hide quoted text - - Show quoted text - sorry for being pain Mike but i think may be i am still not be able to explain to you what i need. *is there any way i can send you my spreadsheet and explain everything on that spreadsheet so you can understant better what i am trying to say- Hide quoted text - - Show quoted text - Hi Mike have you receive my file- Hide quoted text - - Show quoted text - Thanks Mike thats very nice and this has solved my problem. I have just two qestions 1 - what line should i add into your macro that if i have no figure in column D and column F which are "Amount" and "Budget" columns then column G cell get blank or value should be ="". because at the moment if i delete every thing i can still see the balance amount. 2 - My second question is that i can see the remaing balance in column G but what if i want to see this in column F. Because i have deleted all formulas in column G but if i leave them there and in column F it lookup the main budget and if i have taken budget then it give me the balance budget in column F- Hide quoted text - - Show quoted text - Hi Mike thanks for replying for first question. *Please see link below where i have uploded file which will explain you my second question. if you can solve my second question then this will completely solve my whole problem please do reply. ... read more »- Hide quoted text - - Show quoted text - Thanks lot Mike. you dont know how thankful i am to you because you have solved my biggest problem as i am doing project and i needed this macro to finish it. Thanks again. Just small question. can you suggest for me any thing that how can i become good in creating macros. like if you know any book or website. Have you studied about this or just learned yourself because you are quite good |
Please Macro or Formula Help needed
On Dec 28, 2:44 pm, Mike H wrote:
You need a couple of changes to do that, try this but remember if you select multiple cells and delete the macro doesn't work because it doesn't know which is the target cell but that only matters if you delete anything in column D Private Sub Worksheet_Change(ByVal Target As Range) Dim MyRange As Range Set MyRange = Range("D5:D20") If Target.Cells.Count 1 Then Exit Sub If Not Intersect(Target, Range("D5:d20")) Is Nothing Then If IsNumeric(Target) Then On Error Resume Next Application.EnableEvents = False budget = WorksheetFunction.VLookup(Target.Offset(0, 1).Value, Range("j5:n20"), 5, False) For Each c In MyRange If c.Address < Target.Address Then If c.Offset(0, 1).Value = Target.Offset(0, 1).Value Then budget = budget + c.Value End If End If Next c If Target.Value < "" Then Target.Offset(0, 2).Value = budget Else Target.Offset(0, 2).Value = "" End If Application.EnableEvents = True On Error GoTo 0 End If End If End Sub "K" wrote: On Dec 28, 1:56 pm, Mike H wrote: Hvae a look if this does it for you http://www.savefile.com/files/1289003 Mike "K" wrote: On Dec 28, 12:17 pm, Mike H wrote: I'm pleased this answered your problem. Q1, The code I gave you works by knowing which was the active cell when the code is called (the target) so if you select multiple cells and delete them the code doesn't know which is the target because several cells are selected. So to do what you ask would be a largish task and I woiuldn't have started from where I did. However, provided you delete cells in column D as a single cell then this should work. Private Sub Worksheet_Change(ByVal Target As Range) Dim MyRange As Range Set MyRange = Range("D5:D20") If Target.Cells.Count 1 Then Exit Sub If Not Intersect(Target, Range("D5:D20")) Is Nothing Then If IsNumeric(Target) Then On Error Resume Next Application.EnableEvents = False budget = Target.Offset(0, 2).Value For Each c In MyRange If c.Offset(0, 1).Value = Target.Offset(0, 1).Value Then budget = budget + c.Value End If Next c If Target.Value < 0 Then Target.Offset(0, 3).Value = budget Else Target.Offset(0, 3).Value = 0 End If Application.EnableEvents = True 'Allow run time errors again On Error GoTo 0 End If End If End Sub I don't understand the second question. Mike "K" wrote: On Dec 28, 11:04 am, Mike H wrote: Hi, Still no luck with savefile so the file is here http://www.mediafire.com/?3m9nd0jojgs Mike "K" wrote: On Dec 28, 10:01 am, Mike H wrote: Hi Upload the file here and then post the link http://savefile.com/ Mike "K" wrote: On Dec 28, 9:43 am, Mike H wrote: That's what my formula does. Put the 2 formula given in columns B & D and then drag them down as far as required. For (say) project G68 every time you put G68 in column A and a value in column C you will get the remaining budget in column D Mike "K" wrote: On Dec 28, 8:55 am, Mike H wrote: Hi, I think I understand and don't believe you need a macro, try this instead A B C D G68 3980 1000 1980 G68 3980 1000 1980 Column A is a manually input value Column B formula =VLOOKUP(A1,$G$1:$H$5,2,FALSE) Column C is a manually input number Column D formula =VLOOKUP(A1,$G$1:$H$5,2,FALSE)-SUMPRODUCT(($A$1:$A$10=A1)*($C$1:$C$10)) The formula takes the original value and subtracts any manually input values. As you will note this only does the first 10 rows but yu can extend tha ranges to what you want. Mike "K" wrote: Situation:- I have Data in column "G" and "H" (please see belwo) G H G68 3980.00 C47 -4000.00 ME4 -200.00 ME4 200.00 PG2 1200.00 I have "VLOOKUP" formula in column "B" that when ever I put value in any cell of column "A" then it should lookup that value in columns "G" and "H" and get value from 2nd column or column "H". Like if I put "G68" in any cell of column "A" then in column "B" formula should bring up value next to "G68" which will be "3980.00". Then in column "D" I have another formula which is "=B1-C1".. Which gives balance when I put any value in coloumn "C". Like if I have value of "3980.00" in one of column "B" cells and when I put value of "1000.00" in next cell of coloumn "C" then I get balance of "2980.00" by formula in column "D". Macro needed:- I need a macro that when I get balance in column "D" as above mentioned "2980.00" and if I put "G68" again in any cell of column "A" then macro should bring up the balance in column "B" which was "2980.00". Because of the orginal budget was "3980.00" and I have taken "1000.00" as mentioned above and now I want macro to bring balance of budget if I put "G68" again in column "A". Please note that I mentioned "G68" just to explain the situation but it can be any value from column "G" (see table above) I hope you understood what I am trying to say can any one please help me in this. Thanks- Hide quoted text - - Show quoted text - Thanks for replying Mike. I want 1980 to come in column B when i enter G68 next time in column A. Actually what i am trying to do is that in code "G68" i have 3980 budget which come in coloumn B when i enter "G68" in coloumn A and when i put 1000 or any other figure in column C then in coloumn D i get balance that i have taken 1000 from 3980 budget but i want a formula that when i enter G68 again in column A then i want to have same Vlookup formula in column B but this time i want the balance figure which will be 3980 - 1000 = 2980 to appear in column B so i know that i got that much budget left to spend- Hide quoted text - - Show quoted text - sorry for being pain Mike but i think may be i am still not be able to explain to you what i need. is there any way i can send you my spreadsheet and explain everything on that spreadsheet so you can understant better what i am trying to say- Hide quoted text - - Show quoted text - Hi Mike have you receive my file- Hide quoted text - - Show quoted text - Thanks Mike thats very nice and this has solved my problem. I have just two qestions 1 - what line should i add into your macro that if i have no figure in column D and column F which are "Amount" and "Budget" columns then column G cell get blank or value should be ="". because at the moment if i delete every thing i can still see the balance amount. 2 - My second question is that i can see the remaing balance in column G but what if i want to see this in column F. Because i have deleted all formulas in column G but if i leave them there and in column F it lookup the main budget and if i have taken budget then it give me the balance budget in column F- Hide quoted text - - Show quoted text - Hi Mike thanks for replying for first question. Please see link below where i have uploded file which will explain you my second question. if you can solve my second question then this will completely solve my whole problem please do reply. ... read more - Hide quoted text - - Show quoted text - Thanks lot Mike. You don't know how much I am thankful to you because you have solved my biggest problem as I am doing project for my work and this macro has completed my work. Thanks again. Just small question that can you please suggest me anything that how I can become good in creating Macros. Is there any website or Book you can recommend me. Have you studied about this or just learned it yourself as you are quite good in this |
| All times are GMT +1. The time now is 09:42 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com