subtotal with vba(urgent)
Hi, I'm new to vb. pls help me out from this problem. data in excel like this. sttype hr1 hr2 ------ -- --- S7 10 20 S7 30 30 S14 40 30 S14 20 50 OUTPUT. in the last row looks like this. S7 40 50 S14 60 80. I've need output in the last row like above no of row are not fixed . your help is greatly appreciated. Thanks, ramse. -- ramse ------------------------------------------------------------------------ ramse's Profile: http://www.excelforum.com/member.php...o&userid=31544 View this thread: http://www.excelforum.com/showthread...hreadid=512404 |
subtotal with vba(urgent)
Ramse,
I could answer your question but I choose not to. You really need to read something about VBA rather than hoping that someone will write code for you. The best use of a newsgroup is to pose specific questions about subjects you know something about after you've made a best effort to solve the problem for yourself. D "ramse" wrote in message ... Hi, I'm new to vb. pls help me out from this problem. data in excel like this. sttype hr1 hr2 ------ -- --- S7 10 20 S7 30 30 S14 40 30 S14 20 50 OUTPUT. in the last row looks like this. S7 40 50 S14 60 80. I've need output in the last row like above no of row are not fixed . your help is greatly appreciated. Thanks, ramse. -- ramse ------------------------------------------------------------------------ ramse's Profile: http://www.excelforum.com/member.php...o&userid=31544 View this thread: http://www.excelforum.com/showthread...hreadid=512404 |
subtotal with vba(urgent)
I would avoid the VBA for this. I would use either Subtotaling (Data -
Subtotal) or a Pivot Table (Data - Pivot Table). My preference between the two is a pivot table, but either solution will work for you. If you go with the subtotaling then you need to sort your data prior to inserting the subtotals... -- HTH... Jim Thomlinson "ramse" wrote: Hi, I'm new to vb. pls help me out from this problem. data in excel like this. sttype hr1 hr2 ------ -- --- S7 10 20 S7 30 30 S14 40 30 S14 20 50 OUTPUT. in the last row looks like this. S7 40 50 S14 60 80. I've need output in the last row like above no of row are not fixed . your help is greatly appreciated. Thanks, ramse. -- ramse ------------------------------------------------------------------------ ramse's Profile: http://www.excelforum.com/member.php...o&userid=31544 View this thread: http://www.excelforum.com/showthread...hreadid=512404 |
subtotal with vba(urgent)
Hi Dawson, Thanks for your immediate reply. I have tried my level best. I'm able to do total sum but i need to brake like below what i mentioned. here i'm giving my coding i'm not sure where i'm doing wrong. pls help meout. Sub summary() Dim srow As Integer Dim erow As Integer Dim STYPE As String Dim STVALUE As String Dim LROW As Integer Dim nrow As Integer Dim LCONTINUE As Boolean LROW = 0 nrow = 1 Range("a2").Select LCONTINUE = True Do Until Selection.Value = "" Selection.Offset(1, 0).Select Loop endrow = Selection.Row - 1 LROW = LROW + 1 nrow = nrow + 1 STYPE = "A2" STVALUE = "A" & CStr(LROW) For lcount = 1 To endrow 'While LCONTINUE = True If Range(STYPE).Value < Range(STVALUE).Value Then Range("b" & endrow + 2).Formula = "=SUM(b2:b" & endrow & ")" Range("b" & endrow + 2 & ":C" & endrow + 2).FillRight STYPE = "A" & CStr(nrow) End If 'Wend Next lcount End Sub your help is greatly appreciated. Thanks, Ramana. Ramse, I could answer your question but I choose not to. You really need to read something about VBA rather than hoping that someone will write code for you. The best use of a newsgroup is to pose specific questions about subjects you know something about after you've made a best effort to solve the problem for yourself. D "ramse" wrote in message ... Hi, I'm new to vb. pls help me out from this problem. data in excel like this. sttype hr1 hr2 ------ -- --- S7 10 20 S7 30 30 S14 40 30 S14 20 50 OUTPUT. in the last row looks like this. S7 40 50 S14 60 80. I've need output in the last row like above no of row are not fixed .. your help is greatly appreciated. Thanks, ramse. -- ramse ------------------------------------------------------------------------ ramse's Profile: http://www.excelforum.com/member.php...o&userid=31544 View this thread: http://www.excelforum.com/showthread...hreadid=512404 -- ramse ------------------------------------------------------------------------ ramse's Profile: http://www.excelforum.com/member.php...o&userid=31544 View this thread: http://www.excelforum.com/showthread...hreadid=512404 |
subtotal with vba(urgent)
Ramana,
It's hard for me to read your code - maybe it's because there are so many ways to do a thing in VBA and I'm a spoiled C programmer who likes C because it is a compact language that doesn't provide so many choices. In other words, I think I would do things very differently using a smaller set of things I know about VBA. Notwithstanding, here are a few things you need to look at: - What's the purpose of the LROW variable? You set it to 0 then you increment it once. It is essentially an integer constant with a value of 1. It seems as if you had something else in mind for this variable. - Same question for nrow. It is essentially an integer constant of 2. It seems as if you must have intended for these variables to be modified within some loop. - What's going on in your "for loop" when you set Formula and FillRight? These operations end up always being applied to the same cells because nothing in those expressions changes as the loop increments. - Same question for STYPE in your loop - it is a "loop invariant" meaning its value never changes so why do you have this statement inside the loop? In summary, I'm much more convinced than before that you should be learning VBA through a good book rather than trying to solve an "urgent" problem right now. D "ramse" wrote in message ... Hi Dawson, Thanks for your immediate reply. I have tried my level best. I'm able to do total sum but i need to brake like below what i mentioned. here i'm giving my coding i'm not sure where i'm doing wrong. pls help meout. Sub summary() Dim srow As Integer Dim erow As Integer Dim STYPE As String Dim STVALUE As String Dim LROW As Integer Dim nrow As Integer Dim LCONTINUE As Boolean LROW = 0 nrow = 1 Range("a2").Select LCONTINUE = True Do Until Selection.Value = "" Selection.Offset(1, 0).Select Loop endrow = Selection.Row - 1 LROW = LROW + 1 nrow = nrow + 1 STYPE = "A2" STVALUE = "A" & CStr(LROW) For lcount = 1 To endrow 'While LCONTINUE = True If Range(STYPE).Value < Range(STVALUE).Value Then Range("b" & endrow + 2).Formula = "=SUM(b2:b" & endrow & ")" Range("b" & endrow + 2 & ":C" & endrow + 2).FillRight STYPE = "A" & CStr(nrow) End If 'Wend Next lcount End Sub your help is greatly appreciated. Thanks, Ramana. Ramse, I could answer your question but I choose not to. You really need to read something about VBA rather than hoping that someone will write code for you. The best use of a newsgroup is to pose specific questions about subjects you know something about after you've made a best effort to solve the problem for yourself. D "ramse" wrote in message ... Hi, I'm new to vb. pls help me out from this problem. data in excel like this. sttype hr1 hr2 ------ -- --- S7 10 20 S7 30 30 S14 40 30 S14 20 50 OUTPUT. in the last row looks like this. S7 40 50 S14 60 80. I've need output in the last row like above no of row are not fixed . your help is greatly appreciated. Thanks, ramse. -- ramse ------------------------------------------------------------------------ ramse's Profile: http://www.excelforum.com/member.php...o&userid=31544 View this thread: http://www.excelforum.com/showthread...hreadid=512404 -- ramse ------------------------------------------------------------------------ ramse's Profile: http://www.excelforum.com/member.php...o&userid=31544 View this thread: http://www.excelforum.com/showthread...hreadid=512404 |
subtotal with vba(urgent)
Hi cliff, thanks a lot for your response. I'm not a VB guy. basically i'm Oracle guy. suddenly I've need to write a macro for some data which taking data from my procedure. so I'm trying to do this task . I dont have a time to go thru this is very urgent so that is why i posted into the site. I've tried diffrent types .I'm not sure about vb features thats making me tought to write the code. I hope you can understand. tx a lot for ur suggestions. I will do it what every u mentioned. if possible pls helpme out. thanks, Ramana. Cliff Carson Wrote: Ramana, It's hard for me to read your code - maybe it's because there are so many ways to do a thing in VBA and I'm a spoiled C programmer who likes C because it is a compact language that doesn't provide so many choices. In other words, I think I would do things very differently using a smaller set of things I know about VBA. Notwithstanding, here are a few things you need to look at: - What's the purpose of the LROW variable? You set it to 0 then you increment it once. It is essentially an integer constant with a value of 1. It seems as if you had something else in mind for this variable. - Same question for nrow. It is essentially an integer constant of 2. It seems as if you must have intended for these variables to be modified within some loop. - What's going on in your "for loop" when you set Formula and FillRight? These operations end up always being applied to the same cells because nothing in those expressions changes as the loop increments. - Same question for STYPE in your loop - it is a "loop invariant" meaning its value never changes so why do you have this statement inside the loop? In summary, I'm much more convinced than before that you should be learning VBA through a good book rather than trying to solve an "urgent" problem right now. D "ramse" wrote in message ... Hi Dawson, Thanks for your immediate reply. I have tried my level best. I'm able to do total sum but i need to brake like below what i mentioned. here i'm giving my coding i'm not sure where i'm doing wrong. pls help meout. Sub summary() Dim srow As Integer Dim erow As Integer Dim STYPE As String Dim STVALUE As String Dim LROW As Integer Dim nrow As Integer Dim LCONTINUE As Boolean LROW = 0 nrow = 1 Range("a2").Select LCONTINUE = True Do Until Selection.Value = "" Selection.Offset(1, 0).Select Loop endrow = Selection.Row - 1 LROW = LROW + 1 nrow = nrow + 1 STYPE = "A2" STVALUE = "A" & CStr(LROW) For lcount = 1 To endrow 'While LCONTINUE = True If Range(STYPE).Value < Range(STVALUE).Value Then Range("b" & endrow + 2).Formula = "=SUM(b2:b" & endrow & ")" Range("b" & endrow + 2 & ":C" & endrow + 2).FillRight STYPE = "A" & CStr(nrow) End If 'Wend Next lcount End Sub your help is greatly appreciated. Thanks, Ramana. Ramse, I could answer your question but I choose not to. You really need to read something about VBA rather than hoping that someone will write code for you. The best use of a newsgroup is to pose specific questions about subjects you know something about after you've made a best effort to solve the problem for yourself. D "ramse" wrote in message ... Hi, I'm new to vb. pls help me out from this problem. data in excel like this. sttype hr1 hr2 ------ -- --- S7 10 20 S7 30 30 S14 40 30 S14 20 50 OUTPUT. in the last row looks like this. S7 40 50 S14 60 80. I've need output in the last row like above no of row are not fixed . your help is greatly appreciated. Thanks, ramse. -- ramse ------------------------------------------------------------------------ ramse's Profile: http://www.excelforum.com/member.php...o&userid=31544 View this thread: http://www.excelforum.com/showthread...hreadid=512404 -- ramse ------------------------------------------------------------------------ ramse's Profile: http://www.excelforum.com/member.php...o&userid=31544 View this thread: http://www.excelforum.com/showthread...hreadid=512404 -- ramse ------------------------------------------------------------------------ ramse's Profile: http://www.excelforum.com/member.php...o&userid=31544 View this thread: http://www.excelforum.com/showthread...hreadid=512404 |
subtotal with vba(urgent)
Ramana,
You seem desperate and maybe I'm feeling foolish today so I'm giving you the code. If this is part of an independent learning experience or you are a student, then I've done a good deed. However if you have a job and need this to please your boss then I feel I've done you a disservice. You should really tell your boss that you don't know how to solve the problem and that he should anticipate "urgent" situations better and either provide more training for you or find someone else with the right skills. There's no use in setting false expectations. My solution assumes your data matrix starts at the upper left cell and is terminated by a blank row. Also, I think your solution of trying to stick formulas into the worksheet was the wrong approach. Hopefully you'll be able to understand this code sufficiently to see that I took a different approach. Sub subTotal() Dim s7Sub(1) As Long Dim s14Sub(1) As Long Dim currentRow As Integer Dim tag As String Erase s7Sub Erase s14Sub currentRow = 1 tag = Trim(ActiveSheet.Cells(currentRow, 1)) While tag < "" If tag = "S7" Then s7Sub(0) = s7Sub(0) + ActiveSheet.Cells(currentRow, 2) s7Sub(1) = s7Sub(1) + ActiveSheet.Cells(currentRow, 3) ElseIf tag = "S14" Then s14Sub(0) = s14Sub(0) + ActiveSheet.Cells(currentRow, 2) s14Sub(1) = s14Sub(1) + ActiveSheet.Cells(currentRow, 3) End If currentRow = currentRow + 1 tag = Trim(ActiveSheet.Cells(currentRow, 1)) Wend currentRow = currentRow + 1 ActiveSheet.Cells(currentRow, 1) = "S7 subtotal" ActiveSheet.Cells(currentRow, 2) = s7Sub(0) ActiveSheet.Cells(currentRow, 3) = s7Sub(1) currentRow = currentRow + 1 ActiveSheet.Cells(currentRow, 1) = "S14 subtotal" ActiveSheet.Cells(currentRow, 2) = s14Sub(0) ActiveSheet.Cells(currentRow, 3) = s14Sub(1) End Sub "ramse" wrote in message ... Hi cliff, thanks a lot for your response. I'm not a VB guy. basically i'm Oracle guy. suddenly I've need to write a macro for some data which taking data from my procedure. so I'm trying to do this task . I dont have a time to go thru this is very urgent so that is why i posted into the site. I've tried diffrent types .I'm not sure about vb features thats making me tought to write the code. I hope you can understand. tx a lot for ur suggestions. I will do it what every u mentioned. if possible pls helpme out. thanks, Ramana. Cliff Carson Wrote: Ramana, It's hard for me to read your code - maybe it's because there are so many ways to do a thing in VBA and I'm a spoiled C programmer who likes C because it is a compact language that doesn't provide so many choices. In other words, I think I would do things very differently using a smaller set of things I know about VBA. Notwithstanding, here are a few things you need to look at: - What's the purpose of the LROW variable? You set it to 0 then you increment it once. It is essentially an integer constant with a value of 1. It seems as if you had something else in mind for this variable. - Same question for nrow. It is essentially an integer constant of 2. It seems as if you must have intended for these variables to be modified within some loop. - What's going on in your "for loop" when you set Formula and FillRight? These operations end up always being applied to the same cells because nothing in those expressions changes as the loop increments. - Same question for STYPE in your loop - it is a "loop invariant" meaning its value never changes so why do you have this statement inside the loop? In summary, I'm much more convinced than before that you should be learning VBA through a good book rather than trying to solve an "urgent" problem right now. D "ramse" wrote in message ... Hi Dawson, Thanks for your immediate reply. I have tried my level best. I'm able to do total sum but i need to brake like below what i mentioned. here i'm giving my coding i'm not sure where i'm doing wrong. pls help meout. Sub summary() Dim srow As Integer Dim erow As Integer Dim STYPE As String Dim STVALUE As String Dim LROW As Integer Dim nrow As Integer Dim LCONTINUE As Boolean LROW = 0 nrow = 1 Range("a2").Select LCONTINUE = True Do Until Selection.Value = "" Selection.Offset(1, 0).Select Loop endrow = Selection.Row - 1 LROW = LROW + 1 nrow = nrow + 1 STYPE = "A2" STVALUE = "A" & CStr(LROW) For lcount = 1 To endrow 'While LCONTINUE = True If Range(STYPE).Value < Range(STVALUE).Value Then Range("b" & endrow + 2).Formula = "=SUM(b2:b" & endrow & ")" Range("b" & endrow + 2 & ":C" & endrow + 2).FillRight STYPE = "A" & CStr(nrow) End If 'Wend Next lcount End Sub your help is greatly appreciated. Thanks, Ramana. Ramse, I could answer your question but I choose not to. You really need to read something about VBA rather than hoping that someone will write code for you. The best use of a newsgroup is to pose specific questions about subjects you know something about after you've made a best effort to solve the problem for yourself. D "ramse" wrote in message ... Hi, I'm new to vb. pls help me out from this problem. data in excel like this. sttype hr1 hr2 ------ -- --- S7 10 20 S7 30 30 S14 40 30 S14 20 50 OUTPUT. in the last row looks like this. S7 40 50 S14 60 80. I've need output in the last row like above no of row are not fixed . your help is greatly appreciated. Thanks, ramse. -- ramse ------------------------------------------------------------------------ ramse's Profile: http://www.excelforum.com/member.php...o&userid=31544 View this thread: http://www.excelforum.com/showthread...hreadid=512404 -- ramse ------------------------------------------------------------------------ ramse's Profile: http://www.excelforum.com/member.php...o&userid=31544 View this thread: http://www.excelforum.com/showthread...hreadid=512404 -- ramse ------------------------------------------------------------------------ ramse's Profile: http://www.excelforum.com/member.php...o&userid=31544 View this thread: http://www.excelforum.com/showthread...hreadid=512404 |
subtotal with vba(urgent)
Hi Cliff, Intially Thanks for code. Hey cliff you know what is meaning of forum it means sharing of knowldge. ok. If somebody is in trouble we have to share knowledge, better not give the lectures and unnecessary advices, better learn how to help others otherwise keep quite . If u don't want to share better out from the forum. I'm very busy with my other tasks thats why i posted my question.time doen't permit me to do this.ok. But it doesn't work for me. Its static . I'm able to solve my problem. If you want you can use for yourself also. Sub ff() Dim i, j As Long Dim startrw As Long Dim erow As Integer i = 3 j = 2 startrw = 2 Do While Cells(j, 1) < "" erow = erow + 1 j = j + 1 Loop Do While Cells(i - 1, 1) < "" If Cells(i - 1, 1).Value < Cells(i, 1).Value Then If Cells(erow + 3, 2) < "" Then erow = erow + 2 End If Range(Cells(erow + 3, 2), Cells(erow + 3, 4)).Formula = "=Sum(b" & startrw & _ ":b" & i - 1 & ")" startrw = i End If i = i + 1 Loop End Sub Thanks, Ramse. Cliff Carson Wrote: Ramana, You seem desperate and maybe I'm feeling foolish today so I'm giving you the code. If this is part of an independent learning experience or you are a student, then I've done a good deed. However if you have a job and need this to please your boss then I feel I've done you a disservice. You should really tell your boss that you don't know how to solve the problem and that he should anticipate "urgent" situations better and either provide more training for you or find someone else with the right skills. There's no use in setting false expectations. My solution assumes your data matrix starts at the upper left cell and is terminated by a blank row. Also, I think your solution of trying to stick formulas into the worksheet was the wrong approach. Hopefully you'll be able to understand this code sufficiently to see that I took a different approach. -- ramse ------------------------------------------------------------------------ ramse's Profile: http://www.excelforum.com/member.php...o&userid=31544 View this thread: http://www.excelforum.com/showthread...hreadid=512404 |
subtotal with vba(urgent)
Ramana,
I won't debate with you in a newsgroup so this is my last post. Your code is fragile and breaks easily. Try a case in which all the S7 entries are not contiguous and/or all the S14 entries are not contiguous. Maybe you think the data won't be provided that way but if your code has any value it may outlast you and when someone else makes a change to the input your output will be broken. This is why I said putting formulas into the sheet was bad design - it can't be adapted to the simple change in the input that I specified. Want another problem - what's the behavior if there is an error generating the input and S4 values are generated rather than S14? Are you satisfied that your output ignores this issue? I am not saying that my solution is finished or is perfect but it's less likely to easily fall apart. BTW, I have no idea what you mean by my solution being static. C "ramse" wrote in message ... Hi Cliff, Intially Thanks for code. Hey cliff you know what is meaning of forum it means sharing of knowldge. ok. If somebody is in trouble we have to share knowledge, better not give the lectures and unnecessary advices, better learn how to help others otherwise keep quite . If u don't want to share better out from the forum. I'm very busy with my other tasks thats why i posted my question.time doen't permit me to do this.ok. But it doesn't work for me. Its static . I'm able to solve my problem. If you want you can use for yourself also. Sub ff() Dim i, j As Long Dim startrw As Long Dim erow As Integer i = 3 j = 2 startrw = 2 Do While Cells(j, 1) < "" erow = erow + 1 j = j + 1 Loop Do While Cells(i - 1, 1) < "" If Cells(i - 1, 1).Value < Cells(i, 1).Value Then If Cells(erow + 3, 2) < "" Then erow = erow + 2 End If Range(Cells(erow + 3, 2), Cells(erow + 3, 4)).Formula = "=Sum(b" & startrw & _ ":b" & i - 1 & ")" startrw = i End If i = i + 1 Loop End Sub Thanks, Ramse. Cliff Carson Wrote: Ramana, You seem desperate and maybe I'm feeling foolish today so I'm giving you the code. If this is part of an independent learning experience or you are a student, then I've done a good deed. However if you have a job and need this to please your boss then I feel I've done you a disservice. You should really tell your boss that you don't know how to solve the problem and that he should anticipate "urgent" situations better and either provide more training for you or find someone else with the right skills. There's no use in setting false expectations. My solution assumes your data matrix starts at the upper left cell and is terminated by a blank row. Also, I think your solution of trying to stick formulas into the worksheet was the wrong approach. Hopefully you'll be able to understand this code sufficiently to see that I took a different approach. -- ramse ------------------------------------------------------------------------ ramse's Profile: http://www.excelforum.com/member.php...o&userid=31544 View this thread: http://www.excelforum.com/showthread...hreadid=512404 |
All times are GMT +1. The time now is 06:36 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com