VBA 'Cells.Value' no longer returns visible value in cell?

[bfishertarget]

Good Morning-

I have a simple excel macro which is triggered by the worksheet_change event. The macro is coded like this:

For q = 2 To 600
If Worksheets("Threshold Tracker").Cells(q, 1).Value = "" Then
Worksheets("Threshold Tracker").Cells(q, 1).Value = Input1
Worksheets("Threshold Tracker").Cells(q, 2).Value = Date
Worksheets("Threshold Tracker").Cells(q, 3).Value = Time
q = 600
End If
Next q

2 days ago this code worked great, today I open the file and the code will run but no values are showing up in the "Threshold Tracker" cells?? Could there be some setting that adjusted itself?

Thanks in advance,

-B

 

[Andrew Poulsom]

How do you know it's running? What's in A2:A600 on that worksheet?

 

[pjmorris]

The For loop will only run once - with 'q' set to 2? doesn't look right, and what is 'Input1'

 

[bfishertarget]

I can tell it is running because of the notification bar in the bottom corner of excel, also I can run the macro through the visual basic screen and 'step' it along. As for A2:A600, that is just a reference column. If Cell A1 is blank, then fill cells with values. If A1 is not blank, then go to A2 and continue until it finds a blank cell in A. This eliminates the macro from over-writing previous data.

 

[bfishertarget]

Sorry, I may not be clear. Here is the code for the entire worksheet. I start at Q = 2 because row one (or Q=1) is my header row. I also have another worksheet with very similar code, and it too is now acting the same way.

Private Sub Worksheet_Change(ByVal Target As Range)
MSG1 = MsgBox("Changing these thresholds will impact the scheduling tool. Do you wish to continue?", vbYesNo, "Thresholds Changed!")
If MSG1 = vbYes Then
Input1 = InputBox("Please enter your full name (i.e. Last Name,First Name):", "Threshold Changes")

For q = 2 To 600
If Worksheets("Threshold Tracker").Cells(q, 1).Value = "" Then
Worksheets("Threshold Tracker").Cells(q, 1).Value = Input1
Worksheets("Threshold Tracker").Cells(q, 2).Value = Date
Worksheets("Threshold Tracker").Cells(q, 3).Value = Time

q = 600
End If
Next q
Else

Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True

End If

End Sub

 

[Andrew Poulsom]

What's the purpose of?

q = 600

 

[Jonmo1]

What's the purpose of?

q = 600

I would guess that it's purpose is similar to "Exit For"

 

[bfishertarget]

That is correct, once it enters one row of data, set q=600, which will end loop

 

[Norie]

Is the code trying to find the first empty row in column A on sheet 'Threshold Tracker'?

Which row is that?

 

[bfishertarget]

Norie-

All of the code is contained in Microsoft Visual Basic for Worksheet "Thresholds", and the event Worksheet_Change (on "Threshold Tracker" Worksheet) is what executes the code. As stated before, the code worked Monday and today it will run but not put any "text" or "Value" in the desired cells (q1-q4)