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)??
Printable View
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)
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]
Never select
[VBA] .Cells(lastrow + 3, 9).Formula = "=SUM($I$2:I" & lastrow & ")"
[/VBA]
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]
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]
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]
Great, it does work now. Thank you!!
mark the thread solved :)