LinkBack URL
About LinkBacks
Here is my code:
Everything else work accept the red row. I am getting Run time error 13, type mismatch.
Please Login or Register to view this content.
Can anyone tell me where I am going wrong?
Thanks so much,
Angelina
Here is my code:
Everything else work accept the red row. I am getting Run time error 13, type mismatch.Please Login or Register to view this content.
Can anyone tell me where I am going wrong?
Thanks so much,
Angelina
SUMIFS take arguments in this order
RangeToSum, Criteria_Range1, Condition 1, Criteria_Range 2, Condition 2
So...
sheet.Range("A:A"), ("A:A") + WorksheetFunction.SumIfs(sheet.Range("E:E")
is a valid range but not a valid condition combination....
Get the formula to work on a sheet, then you will have a basis to start from...
Bernie Deitrick
Excel MVP 2000-2010
This is the current formula on the sheet. It works well....Something got lost in translation lolPlease Login or Register to view this content.
=SUMIFS(Data!E:E,Data!D:D,"D",Data!A:A,A:A)+SUMIFS(Data!E:E,Data!D:D,"R",Data!A:A,A:A)
This kind of construct - Data!A:A,A:A - in a formula on a sheet picks up the value from column A of the same row in place of the A:A. That won't work in VBA, since you are not placing the formula in a cell - not sure which A you want to refer to - assuming sheet:
ws.Cells(iRow, 4).Value = Application.WorksheetFunction.SumIfs(sheet.Range("E:E"), sheet.Range("D:D"), "D", sheet.Range("A:A"), sheet.Range("A" & iRow).Value) + Application.WorksheetFunction.SumIfs(sheet.Range("E:E"), sheet.Range("D:D"), "R", sheet.Range("A:A"), sheet.Range("A" & iRow).Value)
or possibly (maybe even probably) ws
ws.Cells(iRow, 4).Value = Application.WorksheetFunction.SumIfs(sheet.Range("E:E"), sheet.Range("D:D"), "D", sheet.Range("A:A"), ws.Range("A" & iRow).Value) + Application.WorksheetFunction.SumIfs(sheet.Range("E:E"), sheet.Range("D:D"), "R", sheet.Range("A:A"), ws.Range("A" & iRow).Value)
thanks Bernie,
I tried both. I no longer have the error message so thanks for that, but the cell is not adding as it should. I sending a link for a cut down workbook for you to see. My form should add an new employee row on the "totals" sheet and the formula that I am trying to make work should go in column D of the new row.
Then you should be able to add points from the forms "Add points" tab and you should see it added in the "observer/column D".
https://onedrive.live.com/edit.aspx?...1107&app=Excel
Thanks again,
Angelina
It occurred to me that you might not have a Microsoft account so I tried to attaché a copy of the workbook....however, the uploader was taking an extremely long time....
Its funny, I tried out your second formula and it sort of works. If I enter an new employee on the "totals" sheet, then the formula calculates as "0" which it should. However once I add points into the "data" sheet the formula should calculate them but it doesn't. So then I go and delete the new employee that I added but not the points and re-add on the "totals" sheet and the formula calculates correctly. But when I go and delete points off the "Data" sheet the points are still there....how weird.
Well, that is because you are adding the value that is calculated by the formula, and not the formula itself. Try changing the
ws.Cells(iRow, 4).Value
to
ws.Cells(iRow, 4).Formula = "=SUMIFS(Data!E:E,Data!D:D,"D",Data!A:A,A:A)+SUMIFS(Data!E:E,Data!D:D,"R",Data!A:A,A:A)"
That should add the formula that was working for you....
this gives me a sytex error
I decided to record a macro to see what came up....
this is the working code:
I'm not sure what all this means, but it works so I'll take it!!Please Login or Register to view this content.
Thanks so much Bernie for trying to help.
A~
Sorry - I forgot to double the "s
It is often easier to work with the Formula rather than the FormulaR1C1 since it looks more familiar, and it doesn't need to change if you change what column/row/cell you are putting it into.Please Login or Register to view this content.
That works well too, thanks for the tip!!
Have a fantastic day!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks