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

What Customers are Saying:

 
 
 
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer
< Previous | Next >
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C.
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C.
 
 
 

Meet The Experts:

 
 
 
  • ATLPROG

    ATLPROG

    Computer Software Engineer

    Satisfied Customers:

    7260
    MS in IT.Several years of programming experience in Java C++ C C# Python VB Javascript HTML
< Last | Next >
  • http://ww2.justanswer.com/uploads/SP/spatlanta2010/2011-6-23_12450_photo.64x64.gif ATLPROG's Avatar

    ATLPROG

    Computer Software Engineer

    Satisfied Customers:

    7260
    MS in IT.Several years of programming experience in Java C++ C C# Python VB Javascript HTML
  • http://ww2.justanswer.com/uploads/ComputersGuru/2010-02-13_051118_Photo41.JPG LogicPro's Avatar

    LogicPro

    Computer Software Engineer

    Satisfied Customers:

    5514
    Expert in C, C++, Java, DOT NET, Python, HTML, Javascript, Design.
  • http://ww2.justanswer.com/uploads/lifesaver333/2010-10-17_191349_ls.jpeg lifesaver's Avatar

    lifesaver

    Computer Software Engineer

    Satisfied Customers:

    936
    Several years of intensive programming and application development experience in various platforms.
  • http://ww2.justanswer.com/uploads/EH/ehabtutor/2012-8-2_202016_1.64x64.jpg ehabtutor's Avatar

    ehabtutor

    Computer Software Engineer

    Satisfied Customers:

    766
    Bachelor of computer science, 5+ years experience in software development, software company owner
  • http://ww2.justanswer.com/uploads/eljonis/2010-01-06_130406_eljon2.jpg Eljon's Avatar

    Eljon

    Consultant

    Satisfied Customers:

    558
    Founder of StockCanvas.com
  • http://ww2.justanswer.com/uploads/FA/fael/2011-1-15_104041_profile500x500.64x64.jpg Rafael Martins's Avatar

    Rafael Martins

    Computer Software Engineer

    Satisfied Customers:

    446
    Desktop, Mobile and Web Developer. 7+ years of experience. Creative solutions provider.
  • http://ww2.justanswer.com/uploads/CH/chutz747/2011-11-6_14231_me.64x64.jpg The-PC-Guy's Avatar

    The-PC-Guy

    Software Engineer

    Satisfied Customers:

    320
    Extensive Knowledge in PHP, MYSQL, CSS & Javascript