How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask AAATom Your Own Question
AAATom, Computer Software Engineer
Category: Programming
Satisfied Customers: 468
Experience:  20+ years of experience in embedded programming (assembly, C), Windows programming (VB, user interface design)
Type Your Programming Question Here...
AAATom is online now

in excel vbathe code below was working fine one day then

Customer Question

in excel vba
the code below was working fine one day then the next it came up with
Aplication-defined or Object-defined error need help to fix this error
Private Sub CommandButton1_Click()
If MsgBox("Are you sure you want to Save todays times?", vbYesNo) = vbNo Then Exit Sub
With Range("A6")
.Value = Date
.NumberFormat = "mm/dd/yy"
End With
sDate = Sheets("Time Sheet").Range("A6")
r = 0
For i = 42 To 465
If Sheets("Time Sheet").Cells(i, 2) = sDate Then
r = i
Exit For
End If
Next i
zS = ""
For j = 3 To 14
zS = zS & Sheets("Time Sheet").Cells(r, j) THIS LINE CODE STOPS & PRODUCES ERROR
Next j
If zS "" Then
y = MsgBox("This day has been Saved already", 1)
If y 1 Then
MsgBox "Cancelled!"
End If
Exit Sub
End If

Sheets("Time Sheet").Range("C" & r).PasteSpecial (xlPasteValues)
Application.CutCopyMode = False

Worksheets("Time Sheet").Range("D26:F37").ClearContents
Worksheets("Time Sheet").Range("D7").Select
End Sub
Submitted: 3 years ago.
Category: Programming
Expert:  AAATom replied 3 years ago.

I can help you solve this problem but I need more information.

I checked the code and can reproduce the error - the line you marked produces the error because the variable r is 0 and all ranges in Excel start from 1.

The variable r is 0 because the "For i" loop does not find a cell with the current date.

Now, if this worked before then error happens most likely because the worksheet has been changed.

Beside that, there were another two syntax errors in the code you posted but I guess they are just typos.

Please respond so we can discuss the best way to solve this.

Thank you,

Related Programming Questions