On the attached sheet, how can I say that if cell in column C = "Merrill", then sum the numbers in column I for I2 to lastrow. And place that sum in .Cells(lastrow + 1, 9)??
On the attached sheet, how can I say that if cell in column C = "Merrill", then sum the numbers in column I for I2 to lastrow. And place that sum in .Cells(lastrow + 1, 9)??
Try this
=SUMPRODUCT(--($C$2:$C$6=LEFT($H9,FIND(" ",$H9&" ")-1)),$I$2:$I$6)
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
That works great. However, each sheet will have a different number of rows. So Can I alter this to say instead of $C$2:$C$6 to say $C2: lastrow?
Also, "MERRILL LYNCH" will not always be located in cell $H9. It will always be found in this location:
[VBA]Sub Merrill()
Dim lastrow As Long
Dim i As Long
With ActiveSheet
lastrow = .Cells(.Rows.Count, "C").End(xlUp).Row + 2
.Cells(lastrow + 1, 8).Value = "MERRILL LYNCH"
End With
End Sub[/VBA]
And since there will be a different number of rows each time, I would need to sum $I2:lastrow for those in which column C has "Merrill".
So can we make this a little more generic to reference last rows, etcc..So it will work for many different sheets with different # of rows??
Thanks, this is very helpful!
Maybe something like this to sum the merrill quantities? This doesnt work but perhaps it is a step in the right direction?
[VBA]Sub testingout()
'
Dim lastrow As Long
Dim i As Long
With ActiveSheet
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To lastrow
If .Cells(i, "C").Value = "MERRILL" Then
.Cells(lastrow + 2, 9).Select
ActiveCell.FormulaR1C1 = "=sum(I2:I)"
End If
End With
End Sub[/VBA]
This places the sum in the right place but it does not generate a sum. How do I say to I2 to lastrow?
[VBA]Sub testingout()
'
Dim lastrow As Long
Dim i As Long
With ActiveSheet
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To lastrow
If .Cells(i, "C").Value = "MERRILL" Then
.Cells(lastrow + 3, 9).Select
ActiveCell.FormulaR1C1 = "=sum($I$2:I)"
End If
Next i
End With
End Sub
[/VBA]
[VBA]ActiveCell.Formula = "=SUM($I$2:$I$" & lastrow & ")"[/VBA]
------------------------------------------------
Happy Coding my friends
Never select
[VBA] .Cells(lastrow + 3, 9).Formula = "=SUM($I$2:I" & lastrow & ")"
[/VBA]
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Thats works great! But it seems like this part inst working for me:
[VBA]If .Cells(i, "C").Value = "MERRILL" Then
[/VBA]
I am trying to sum the cells in column I in which they have "Merrill" in Column C
try
[VBA]If InStr(.Cells(i, "C").Text,"MERRILL") > 0 Then[/VBA]
------------------------------------------------
Happy Coding my friends
See the attached. If you try the code it is still calculating the sum of everything in colum I regardless of what is says in column C.
[VBA]Sub testingout()
'
Dim lastrow As Long
Dim i As Long
With ActiveSheet
lastrow = .Cells(.Rows.Count, "C").End(xlUp).Row
For i = 2 To lastrow
If InStr(.Cells(i, "C").Text, "MERRILL") > 0 Then
With .Cells(lastrow + 3, 9).Select
ActiveCell.Formula = "=SUM($I$2:$I$" & lastrow & ")"
End With
End If
Next i
End With
End Sub[/VBA]
I think it isn't working becasue this line is messing it up
[VBA]With .Cells(lastrow + 3, 9).Select
[/VBA]
But somehow i still need to reference that cell to place the sum in
[VBA]Sub testingout()
'
Dim lastrow As Long
Dim i As Long
With ActiveSheet
lastrow = .Cells(.Rows.Count, "C").End(xlUp).Row
For i = 2 To lastrow
If InStr(.Cells(i, "C").Text, "MERRILL") > 0 Then
.Cells(lastrow + 3, 9).Value = .Cells(lastrow + 3, 9).Value + .Cells(i, "I").Value
End With
End If
Next i
End With
End Sub[/VBA]
------------------------------------------------
Happy Coding my friends
Thats not working bc it is not suming the correct cells. In the attached excel sheet my ultimate goal is to sum I2 to I5 because those correspond to MERRILL (in Column C). And that sum to be placed in .Cells(lastrow + 3, 9).Value
i just tested this on your spreadsheet and it works:
[VBA]Sub testingout()
Dim lastrow As Long
Dim i As Long
With ActiveSheet
lastrow = .Cells(.Rows.Count, "C").End(xlUp).Row
For i = 2 To lastrow
If InStr(.Cells(i, "C").Text, "MERRILL") > 0 Then
.Cells(lastrow + 3, 9).Value = .Cells(lastrow + 3, 9).Value + .Cells(i, "I").Value
End If
Next i
End With
End Sub[/VBA]
------------------------------------------------
Happy Coding my friends
Great, it does work now. Thank you!!
mark the thread solved
------------------------------------------------
Happy Coding my friends