NEED HELP URGENT!!!

Posted by Colin on November 16, 2001 8:00 AM

I need to develop a macro to put the sum formula into a cell in a worksheet the following code won't work because it throws an object or application-defined error:
Sub twelve()
ActiveCell.Select
r = Selection.Row
c = Selection.Column
Selection = "=SUM("
Worksheets("salaries").Activate
For counter = 10 To 850
If Cells(counter, 2) = "1200" Then
Worksheets("2001-2002").Cells(r, c) = Worksheets("2001-2002").Cells(r, c) + "salaries!L" + CStr(counter) + "+"
End If
Next
Worksheets("2001-2002").Activate
End Sub

All I need to knowis how this code needs to be changed in order to make the code work and print the sum function in the cell.

Thanks

Colin

Posted by Barrie Davidson on November 16, 2001 8:11 AM

Why not use this formula,

=SUMIF(Salaries!B10:B850,"=1200",Salaries!B10:B850)

Does this help you out?Barrie Davidson

Posted by Colin on November 16, 2001 9:57 AM

It worked fine but I need to use it in a macro because I have a huge amoiunt of data and I need to use different ranges and numbers for each set.
I would like to be able to get VB to print with = at the begining of the cell, but I can't do it.

Any help is appreciated

Posted by Barrie Davidson on November 16, 2001 11:12 AM

Colin, not sure if this would work for you. The code will prompt the user for the criteria (1200 in your example), and then put the SUMIF formula in the active cell. You would need to change the lookup range (currently "Salaries!B10:B850").

Sub SumIf()
Dim SumCriteria As String
SumCriteria = """" & "=" & InputBox("Enter criteria") & """"
ActiveCell.Formula = "=SUMIF(Salaries!B10:B850," & _
SumCriteria & ",Salaries!B10:B850)"
End Sub

Does this help you?
Barrie
Barrie Davidson