0
Sub Off_Hours_Set_TEST()
      Dim x As String
      Dim found As Boolean
      Dim i As Integer, j As Integer

' Select first line of data.
      Range("A1").Select
' Set search variable value.
      x = "Off"

    For i = 0 To 2
        For j = 0 To 10
            If ActiveCell.Value = x Then
                found = True
                ActiveCell.Offset(i, j + 2) = "0"
            Else
                ActiveCell.Offset(i, j + 2).Value = ActiveCell.Offset(i, j + 1).Value - ActiveCell.Offset(i, j).Value
            End If
            j = j + 2
        Next j
    Next i
End Sub

Trying to make a little code that tallies up hours worked for day. It works until it encounters the cell with the word 'Off' in it, then it RTE 13's on me. I'm not quiet sure why it does this, or where the mismatch is coming from, as all it is doing is checking to see if the cell = Off, if it does, it inputs a 0 in the offset hours worked column. Ideas?

Community
  • 1
  • 1
Brandon Burrows
  • 13
  • 2
  • 1
    The word "Off" can't be subtracted from anything. (I assume it crashes at the part that says `- ActiveCell.Offset(i, j).Value`) – YowE3K Jul 17 '17 at 20:14
  • 1
    You are always testing A1 The active cell never changes. – Scott Craner Jul 17 '17 at 20:15
  • Must read: [How to avoid using Select in Excel VBA macros](https://stackoverflow.com/q/10714251/1188513) – Mathieu Guindon Jul 17 '17 at 20:25
  • Consider using `Range.Find` instead of looping. – Mathieu Guindon Jul 17 '17 at 20:30
  • Ya I just realized the A1 thing being the basis that it goes off of for *everything*. So how can I set the starting cell, and have the loop just go through what I need? – Brandon Burrows Jul 17 '17 at 20:45
  • please read comment posted by @Mat'sMug that refers to "Must Read" – jsotola Jul 18 '17 at 01:41
  • 1
    ActiveCell can change with disastrous consequences. run this and then "accidentally" click on cells around the worksheet while it is running `Sub testActivecell()` `While 1` `ActiveCell.Interior.ColorIndex = Rnd() * 20` `DoEvents` `Wend` `End Sub` – jsotola Jul 18 '17 at 02:05

0 Answers0