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
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)
66411959
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()
ActiveSheet.Unprotect
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)
ActiveSheet.Protect
If y 1 Then
MsgBox "Cancelled!"
ActiveSheet.Protect
End If
Exit Sub
End If

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

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

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,
Tom

Related Programming Questions