0

I might be not understanding something here. I try to look for the last row in a datasheet and sum up a range. But for some reason it spits out 0, for both values (there are numbers in the cells.). 

 lRow = Cells.Find(What:="*", _
                    After:=Range("A1"), _
                    LookAt:=xlPart, _
                    LookIn:=xlFormulas, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False).Row

Offene_PZ_Form.AZ_Lager.Caption = Application.Sum(Worksheets("Data").Range(Cells(3, 19), Cells(lRow, 19)))
Offene_PZ_Form.AZ_Fehlt.Caption = Application.Sum(Worksheets("Data").Range(Cells(3, 20), Cells(lRow, 20)))
Leon S
  • 103
  • 1
  • 7
  • `Range(Cells, Cells)` is problematic... see [this](https://stackoverflow.com/questions/8047943/excel-vba-getting-range-from-an-inactive-sheet). – BigBen Jun 02 '20 at 14:00
  • do you have `On Error Resume Next` in your code? – Scott Craner Jun 02 '20 at 14:00
  • @ScottCraner No, I don't... – Leon S Jun 02 '20 at 14:01
  • Do the cells contain *text-that-looks-like-numbers*? What is the result if you just manually `=SUM()` that range? – BigBen Jun 02 '20 at 14:02
  • Well. So the cells (i call it cell1) are referencing to another cell (cell2), which contents are formatted as text (cell 2 comes from a textbox), and cell1 has an if clause in it (`=IF(A10=FALSE;J10;0)`. So cell 2 will be a text as well? (Excel doesnt mark it as text tho) (Sum is 0) – Leon S Jun 02 '20 at 14:06
  • 1
    Yes it will be text... You can force it to be a number with `--` – BigBen Jun 02 '20 at 14:07
  • 2
    if `J10` is text then the formula will return text. – Scott Craner Jun 02 '20 at 14:08
  • where do I need to - - ? – Leon S Jun 02 '20 at 14:08
  • 2
    `=IF(A10=FALSE;--J10;0)` – Scott Craner Jun 02 '20 at 14:08
  • 2
    Thanks, didn't know that works, getting smarter every question i ask on here lol! SO's member are (almost everytime) so helpful. thank you guys – Leon S Jun 02 '20 at 14:10
  • 1
    You could also do `=NOT(A10)*J10` If it is either `TRUE` or `FALSE` – Scott Craner Jun 02 '20 at 14:10
  • The true/false statement comes from a checkbox, so that would be possible too. I added `PZE_RNG.Offset(0, 17) = "=IF(RC[-18]=FALSE,RC[-9],0)"` to some line of code I need (to put in the formula relative to the cell "PZE_RNG"). would this be converted to `PZE_RNG.Offset(0, 17) = "=IF(RC[-18]=FALSE,--RC[-9],0)"`? – Leon S Jun 02 '20 at 14:14

0 Answers0