0

I put in my 5 hours trying to figure this out, and now it's time to ask for help.

I am creating an "Inventory calendar" where each day we produce and sell a number of items. I want to see how many day's worth of an item I have on a particular day. The number of items we sell each day is different. Everything works until the If LastRowQ.Offset(-1,0).Value line where I get an "Object required" error. I want to divide the number of items that are left the second to last cell (the last cell is offset(-1,0) because the last cell results in a negative if it is not a 0) by the number of items sold that day (column H) so I get a decimal of how much of that day will be covered by the inventory we already have. Also, how do I get that value (remainder) to be put in cell R3?

Thanks!

Sub test()
Dim r As Integer, a As Integer, remainder As Single
remainder = 0
  Range("B2").Select ' Today's inventory
Application.CutCopyMode = False
Selection.Copy
Range("Q2").Select ' Helper column to paste the remaining inventory for each day
ActiveSheet.Paste
For r = 2 To 10
  While Cells(r, "Q").Value > 0
        Cells(r + 1, "Q") = Cells(r, "Q").Value - Cells(r, 8).Value
        r = r + 1
  Wend
Next r
With ActiveSheet
LastRowQ = .Range("Q" & .Rows.Count).End(xlUp).Row
End With
If LastRowQ.Offset(-1, 0).Value > 0 Then
remainder = LastRowQ.Offset(-1, 0).Value / LastRowQ.Offset(0, -9).Value
' How do I put the value of "remainder" into cell R3?
End If

End Sub
Community
  • 1
  • 1
ShNBl84
  • 55
  • 1
  • 12
  • 1
    What programming language is this? You need to tag your question with the name of the language you're using. – jwodder Jun 21 '17 at 14:55
  • Excel VBA. Thank you for helping me clarify. – ShNBl84 Jun 21 '17 at 15:01
  • [Avoid Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) and qualify all your ranges to parent worksheets and you will probably solve the issue. `Worksheets("mySheet").Range("R3").Value = remainder` – Scott Holtzman Jun 21 '17 at 15:10
  • I added "ActiveSheet.Range("R3").Value = remainder" at the end of my code (thanks Scott), but I don't know if it is working because I am still getting the object error. I read the link about avoiding select, but I could not find how to copy/paste cells after setting a value in that way. – ShNBl84 Jun 21 '17 at 15:38
  • Fix the statement `LastRowQ = .Range("Q" & .Rows.Count).End(xlUp).Row` by putting a `Set ` on the front of it. You need to use `Set` when assigning objects or bad/weird things can happen. Alternatively, use `OPTION EXPLICIT` and with explicit data-typing the compiler can often figure out when you should've used a `Set`. – RBarryYoung Jun 21 '17 at 15:50

1 Answers1

1

  1. After seeing Range("B2").Select ' Today's inventory should the 8 in Cells(r + 1, "Q") = Cells(r, "Q").Value - Cells(r, 8).Value be a B like Cells(r + 1, "Q") = Cells(r, "Q").Value - Cells(r, "B").Value ?

  2. LastRowQ = .Range("Q" & .Rows.Count).End(xlUp).Row does not set a range object; it assigns a number to LastRowQ. You cannot use use LastRowQ as a range object like LastRowQ.Offset(-1, 0).Value. Perhaps you meant Cells(LastRowQ - 1, "Q").Value.

  3. Use Option Explicit to avoid variable declaration errors and typos.

  4. Explicitly qualify all parent worksheets.

Graham
  • 7,431
  • 18
  • 59
  • 84
  • Jeeped - I have simplified my code a bit for the sake of clarity, which is why you may be confused about the formatting in the beginning of the code. I took your advice and made the #2 change and now that line seems to be working, and now I am getting a "Application-defined or object-defined error" on the next line (remainder = etc etc). With ActiveSheet LastRowQ = .Range("Q" & .Rows.Count).End(xlUp).Row End With If Cells(LastRowQ - 1, "Q").Value > 0 Then remainder = Cells(LastRowQ - 1, "Q").Value / Cells(LastRowQ, -9).Value ActiveSheet.Range("R3").Value = remainder – ShNBl84 Jun 21 '17 at 15:52
  • The change described in step 2 should be done in at least three places. –  Jun 21 '17 at 15:54
  • Yes, I made those changes in three places: the error row and twice in the following row. The row where I made the two additional changes is now the current error row. – ShNBl84 Jun 21 '17 at 15:58
  • Then I can only assume that `Cells(LastRowQ - 1, "H").Value` is zero and you cannot divide by zero. You check the numerator of the division operation, not the denomonator. –  Jun 21 '17 at 16:00
  • That is a good guess, but that cell is not a 0 (I even tested the adjacent columns to make sure I did not put in the wrong column and still got the same error). – ShNBl84 Jun 21 '17 at 16:05
  • OK, that code is going to try to fill column Q all the way to the bottom. You are looping a while... wend inside a for ... next as well as iterating the for ... next loop's r variable. Get rid of the while ... wend and just use `if Cells(r, "Q").Value <= 0 then exit for`. –  Jun 21 '17 at 16:09