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