Application-defined or object-defined error (1004) in VBA, when assigning value from range

Question

My code gives the error stated in the title and I don't know why, it happens in this line:

Time = KNP.Cells(2, LastVans) - KNP.Cells(2, FirstVans)

Everything I could but nothing seemed to work...

Sub CalculateKPI()

Dim RKPI, KNP, ST As Worksheet
Set RKPI = Sheets("Results KPI")
Set KNP = Sheets("SimNodes")
Set ST = Sheets("SimStartTimes")
Dim LastVans As Long
Dim FirstVans As Long
Dim Time As Long

  RKPI.Cells(1, 2) = "Maximum Crowdedness"
  RKPI.Cells(1, 3) = "Occupation Time"

  '### Einde Stap 2

  For i = 1 To 25
       RKPI.Cells(i + 1, 1) = "Stage" & i

      lastcol = Split(KNP.Cells(i + 2, 
  Columns.Count).End(xlToLeft).Address, "$")(1)

      TotalMaximumCrowdedness(i) = TotalMaximumCrowdedness(i) + 
  Application.WorksheetFunction.Max(KNP.Range("B" & i + 2 & ":" & lastcol 
& i + 2))

    '## KPI occupationtime per stage
    lastcolnr = KNP.Cells(i + 2, Columns.Count).End(xlToLeft).Column

    FirstVans = 0
    LastVans = 0
Dim g As Long
 For g = 1 To 24
    For p = 2 To lastcolnr
        If KNP.Cells(g + 2, p) <> 0 Then
            FirstVans = p
            Exit For
        End If
    Next

    Next g

    For p = lastcolnr To 2 Step -1
        If KNP.Cells(, p) <> 0 Then
            LastVans = p
            Exit For
        End If
    Next

    Time = KNP.Cells(2, LastVans) - KNP.Cells(2, FirstVans)
    TotalMaximumCrowdedness(i) = TotalMaximumCrowdedness(i) + Time

     lastcolnr = KNP.Cells(i + 2, Columns.Count).End(xlToLeft).Column

    Next

       Call Costs
         Call KPI3
        Call BusinessStageChange

 End Sub

Any help would be appreciated, I am pretty new to VBA. If anyone needs the file to help out, I would be pleased to supply it.

If you step through the code in the debugger to see exactly what's being executed, it should tell you that one of your loops that is setting LastVans or FirstVans is not running. Learning to use the debugger is one of the most powerful additions a programmer can make to their toolbox - it's never too soon to start to use it. — Ken White
– Ken White, Commented Oct 31, 2019 at 16:49
If KNP.Cells(, p) <> 0 Then - is that intended as Cells(1, p) ? Might also want to reset FirstVans/LastVans inside each g loop iteration, unless you expect values to "carry over" between iterations. — Tim Williams
– Tim Williams, Commented Oct 31, 2019 at 16:57

Scott Craner · Accepted Answer · 2019-10-31 18:36:25Z

1

Time = KNP.Cells(2, LastVans) - KNP.Cells(2, FirstVans)

returns application-defined or object-defined error, because LastVans and FirstVans are 0. Thus it is evaluated to KNP.Cells(2,0) and the 0 should stand for the column. This is automatically an error, because the first column is 1.

As an advice to avoid this, you may consider the following check:

If LastVans > 0 And FirstVans > 0 Then
    Time = KNP.Cells(2, LastVans) - KNP.Cells(2, FirstVans)
Else
    MsgBox "Vans less than 1."
End If

edited Oct 31, 2019 at 18:36

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

answered Oct 31, 2019 at 16:56

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vityata Over a year ago

@Scott Cramer. True. Can you please edit the answer, I am away from keyboard.

Collectives™ on Stack Overflow

Application-defined or object-defined error (1004) in VBA, when assigning value from range

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related