Runtime Error 1004 in Company Employee Timesheet Spreadsheet

[mjoseph527]

Hello, we've been using this spreadsheet as a timesheet since May of 2002. Recently in the past couple of days when we try to update an employee's time and date of work done by clicking on an update button on the first Worksheet, an error (Runtime Error '1004' Method 'Range' of object '_Global' failed) pops up.

Clicking the 'Debug' button opens a window up with this information:

Please Login or Register  to view this content.

The "Range("A" & vCurrentRow).Value = vDate" is what is highlighted in yellow.

After looking you will see that the Worksheets with the problem are called "Daily" and "Trans".

I don't know what I'm looking at, all I know how to do is enter the information and press that update button. The guys that help set this system up are no longer employed here and we need to get this going ASAP.

Any help would be greatly appreciated.

Thanks in advance.

[mewingkitty]

It's kinda hard for me to tell, someone else may be able to spot it right off the bat, but I can say that without defining vcurrent row as anything, it should default to 0.

So it's looking for range A0, which doesn't exist.

[mjoseph527]

Thanks for the reply.

But, if that is true then why has it been working for 6.5 close to 7 years now.

I'm all confused

I myself haven't ever seen the code behind a excel spreadsheet before, this is my first time.

[mjoseph527]

Hasn't anyone ever run across this problem before? We are having to take down all employee information down on paper or other documents currently and it is very inconvenient.
Please help if anyone can, thank you.

[Paul]

When working with multiple worksheets, it's best to always preface a range with the sheet you want to reference. See your updated code below:

Please Login or Register  to view this content.

You could also use With / EndWith statements instead, which might make it a bit cleaner:

Please Login or Register  to view this content.

[mjoseph527]

Hey Thanks,
I tried them both and got the same error in the same location for the first suggestion and got "Compile Error: End With Without With" on the first "End With" statement from the second suggestion.

[mjoseph527]

Here is more of the code after altering it with the "With" commands if it will help.

Please Login or Register  to view this content.

[mjoseph527]

I tried changing this

Please Login or Register  to view this content.

To This

Please Login or Register  to view this content.

I then get an error telling me "compile error: invalid or unqualified reference" with .range highlighted here:

Please Login or Register  to view this content.

[shg]

How about posting the entire original subroutine for the code the generates the error, tell us the line that generates the error, and the error message?

[mjoseph527]

I'll try to get this right, and I made sure to not include any confidential info.

Please Login or Register  to view this content.

The error message is as follows: "Runtime Error '1004' Method 'Range' of object '_Global' failed"

I highlighted the areas in yellow that are highlighted in yellow in the debugger.

[mjoseph527]

Did I not post the right information or is everyone as stumped as me

[shg]

What is the value of vCurrentRow when the code errors?

As a matter of good practice, you should put Option Explicit at the top of the module and declare variables appropriate to usage until the code compiles.

[mewingkitty]

Yaknow, I thought I had something there, but if SHG hasn't figured this out, I'm sure I wasn't right.

I keep getting a "Variable not defined" on that line about changing cells which aren't yellow back into having no color. My comp doesn't recognize "None" as anything. Treats it as a variable which is not defined.

[shg]

if SHG hasn't figured this out, I'm sure I wasn't right

Dunno about that. I just don't debug code with undeclared variables.

[mewingkitty]

Please Login or Register  to view this content.

Alrighty, that ran fine. Has option explicit enabled. I changed the "none" into "2". It whites the cells right out, but I can't get it to compile as "none".

So
Questions.

What value do you have in A600 of the "Daily" sheet?

Do you still have three sheets with the names "Trans", "Daily", "EmpMaster" ?

[mjoseph527]

Please Login or Register  to view this content.

Alrighty, that ran fine. Has option explicit enabled. I changed the "none" into "2". It whites the cells right out, but I can't get it to compile as "none".

So
Questions.

What value do you have in A600 of the "Daily" sheet?

Do you still have three sheets with the names "Trans", "Daily", "EmpMaster" ?

8 is the value in A600

And yes, all three sheets exist.

[mjoseph527]

If I knew this was written that badly I wouldn't have bothered you guys and I greatly appreciate all the help you have tried to give me, but it has taken too long to fix and we have decided to purchase a program instead of using the excel spreadsheet or hiring someone to write us a database in access. We'll just have to enter all the info in the new program.

If we ever need any excel work we can't figure out we'll post here again. :D

Thank you for looking at it and could an Admin go ahead and mark this as solved.